Import dynamic text file - experience?

Question

Import dynamic text file - experience?

Jason Hairston

SSC Enthusiast

Points: 106
More actions
July 20, 2005 at 9:03 am

#67220

Hi. I'm new here. This is a wonderful site, with endless helpful and interesting information. I'm fairly new with advanced SQL functions. Mainly my experience is with basic admin tasks. However, I've got something I'm working on that is giving me a little trouble. It seems like DTS would be a good way to do what I want. I looked on this site a little, but didn't see an article that seemed to cover this. This is an advanced subject for me and I’m hoping that it is routine for someone else. So, if anyone just enjoys helping other SQL DBA's out, then please give me some feedback.
We have an application that creates a new log file everyday. (ex. MO20050720.log). Each file is appended throughout the day.
Our goal: Import these files into SQL. Then view the data via XML on a web page.
We would like to be able to view up-to-the-minute data. This means that log files from earlier days must remain available. Plus we want to set up a polling event to pull in the data from the current day's file. We will be going forward and not importing data from past days, but want to keep all data imported as we move forward.
I have started a DTS package, but I'm getting stuck on a few issues.
My questions:
1. How do I import a text file (.log) if I want to change the name (source) everyday? And how can I automate this to know which file to import based on the current date?
2. Is it possible or more efficient to import into a single table? Or import into a new table every day?
3. Because we will be importing the same file several times a day, is there a way to keep from duplicating the data being imported?
Thank you,
Jason

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

dm Ten Centuries Points: 1135 More actions · Answer 1

Answers to your questions:

1. You can use VBScript within a ActiveX Task to change the name of the file you want to import. Check this article out:

http://www.sqldts.com/default.aspx?200

You can also use the Dynamic Properties tasks too.

2. This really varies and depends on the number of rows and your in environment in general. You will have to test on this one. Also, keep in mind how you want to query the data. If you want to query more than one day at a time, having a table per day will make it more diffucult.

3. You could import the data first into a staging table, then compare that data to the previously imported data in another table, and only insert the new data.

Hope this helps.

Diane

Jason Hairston SSC Enthusiast Points: 106 More actions · Answer 2

Thank you for the reply, Diane. That is very helpful.

I have one follow-up question:

1. In the Design DTS screen, do I need to start out with the ActiveX task/Dynamic Properties Task? My concern is the Datasource location for my text file connection is hard coded to a network location. So, does the ActiveX/Dynamic Task replace what is in the the text connection properties?

Thanks again.

Jason

Vítor Nuno Viana Ferreira SSC Veteran Points: 235 More actions · Answer 3

Hi,

you can use 3 diferent Global Variables:

1 - You Path (without file name)

2 - Your original Filename

3 - You final FileName (the one ActiveX task will change with first and second variables plus your current date)

Once the 3rd variable is changed by ActiveX Task you can change your File source with Dynamic task. Associating the Source path to this Global Variable.

Hope this helps,

Vítor

dm Ten Centuries Points: 1135 More actions · Answer 4

Jason,

Yes, the ActiveX/Dynamic Properties task comes first in the DTS workflow so that it will change the text connection properties before you connect to the file and import the data.

Diane

Jason Hairston SSC Enthusiast Points: 106 More actions · Answer 5

Thanks for the replies. I think I’m starting to put things together. But I think I may be complicating it more than I need to. So I have a couple of specific questions:

1. What goes in the value for the Global Variable?

A. "MO" + convert(char(8),getdate(),112) +".log"

or

B. \\servername\c$\Program Files\RGE INC\IPSentry\LogFiles

I have created a Dynamic Task

2. Under Dynamic Properties Task: Package Properties, do I set the Value for:

Connection1-OLE DB Properties to

A. A Global Variable

B. a query which contains: "MO" + convert(char(8),getdate(),112) +".log"

3. Will I need an ActiveX task if I use the Dynamic Task?

Jason

dm Ten Centuries Points: 1135 More actions · Answer 6

Jason,

The answer to 3 is depended on the answer to 3. You can use either an ActiveX task or the Dynamic Properties Task. Let me show you some code for an ActiveX task.

I'll assume your file name format is something like MyLog_YYYYMMDD.txt where YYYYMMDD is the date like 20050720, today's date.

First, assign the first part of the file name to the global variable:

DTSGlobalVariables("FileName").Value = "\\MyServer\MyDirectory\MyLog_

Now get the current date and put it in a MMDDYYYY format

intMonth = month(Date())

intYear = year(Date())

intDay = day(Date())

sFileDate = intYear & Right("00" & intMonth,2) & Right("0" & intDay,2)

Now put it all together:

DTSGlobalVariables("FileName").Value = DTSGlobalVariables("FileName").Value & sFileDate & ".txt"

And you have your file name

then assign it to the connection for the file

set oConnection = oPackage.Connections("MyFileConnection")

oConnection.DataSource = DTSGlobalVariables("FileName").Value

and then you are done

Cheers

Diane

Jason Hairston SSC Enthusiast Points: 106 More actions · Answer 7

That helps a lot, Diane.

I have set up the following ActiveX task:

-------------------------------------------------------------------

Option Explicit

Function Main()

Dim intYear, intMonth, intDay, sFileDate, oConnection

DTSGlobalVariables("Filename").Value="\\pmsan\c$\Program Files\RGE INC\IPSentry\LogFiles\MO"

intYear=year(Date())

intMonth=month(Date())

intDay=day(Date())

sFileDate=intYear & Right("0" & intMonth,2) & Right("0" & intDay,2)

DTSGlobalVariables("Filename").Value=DTSGlobalVariables("Filename").Value & sFileDate & ".log"

set oConnection=cPackage.Connections(Conn1)

oConnection.DataSource=DTSGlobalVariables("Filename").Value

End Function

----------------------------------------------------

When running it, I get this error message:

Variable is undefined: 'cPackage'

Have I left something out?

Jason

Vítor Nuno Viana Ferreira SSC Veteran Points: 235 More actions · Answer 8

before you do "set oConnection=cPackage.Connections(Conn1)"

you should right this instruction

"set cPackage = DTSGlobalVariables.Parent"

Vítor

Jason Hairston SSC Enthusiast Points: 106 More actions · Answer 9

Thank you Vitor and Diane. I was able to get my process working successfully. I'm trying to add one more thing to the coding, in order to keep a running list in my SQL. Each time I run the import I will want to delete all previous entries from the same day. It looks at Column 1 in the SQL table, and compares the entries to today's date. Then it will delete all rows that were imported today, leaving previous day's data there. Here is a select statement to check the data:

'View the rows that contain today's date'

select * from MO20050713

where Col001 =

(convert(varchar(10),getdate(),101))

It is not returning any data.

however, this works:

select * from MO20050713

where Col001 = '7/22/2005'

I know that "(convert(varchar(10),getdate(),101))" returns the same date format, so I'm stuck. Not sure why it doesn't recognize and match the date format when putting the code together.

dm Ten Centuries Points: 1135 More actions · Answer 10

Well, select convert(varchar(10),getdate(),101) returns this

07/22/2005

which is different than

7/22/2005

if we are talking about charater data. You will have to strip off the leading zero.

Diane