July 20, 2005 at 9:03 am
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
July 20, 2005 at 10:29 am
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
July 20, 2005 at 12:39 pm
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
July 20, 2005 at 12:57 pm
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
July 20, 2005 at 2:05 pm
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
July 20, 2005 at 2:54 pm
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
July 20, 2005 at 3:49 pm
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
July 21, 2005 at 8:44 am
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
July 21, 2005 at 9:10 am
before you do "set oConnection=cPackage.Connections(Conn1)"
you should right this instruction
"set cPackage = DTSGlobalVariables.Parent"
Vítor
July 22, 2005 at 8:39 am
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.
July 22, 2005 at 11:50 am
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply