March 11, 2009 at 7:55 am
I have a package that imports a file into a table.
The package is initiated by an end-user on demand (via Excel/VB) The file name varies frequently.
The file is set dynamically via a Dynamic Propertiers Task using the query...
SELECT Filename
FROM dbo.DTSDataSourceFiles
WHERE (DataSourceName = 'Current TB')
This all works fine, however I now need to log the date and time that the data was created in the database in such a way that I can return the latest set of data in a view. If I use Now() to produce this date, then it will span over several seconds/minutes and therefore you can't determined the last 'set' that was imported. The data could be imported several times a day so it has to record down to the minute/second.
Ideally I would like to use the creation date/time of the imported file to as my dataset time.
so, my problem is, how can I extract the creattion datetime of the file produced by the query above and then use that value in my Transform task to write to a field in the table.
I don't have any experience (yet!) of using variables in DTS
March 11, 2009 at 8:07 am
If you're trying to get the the date and time from the file itself then you'll have to use an ActiveX script to do that and then pass the value to a variable. I don't have the information for doing that at my fingertips right now so I cannot give you much more details at this time.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
March 11, 2009 at 8:20 am
Hi Alvin
I know how to get a filedate by activex...
Dim oFSO
Dim oFile
Dim sSourceFile
Set oFSO = CreateObject("Scripting.FileSystemObject")
sSourceFile = DTSGlobalVariables("SourceFileName").Value
Set oFile = oFSO.GetFile(sSourceFile)
msgbox oFile.DateCreated
' Clean Up
Set oFile = Nothing
Set oFSO = Nothing
what I don't know is how to extract the filename product by the Dynamic Properties Task to use in the script (in place of "SourceFileName")
I also don't know how to use the value of oFile.DateCreated for use elsewhere in the package
March 11, 2009 at 8:47 am
I don't have SQL 2000 available right now so I cannot test this.
You should be able to get the Dynamic Properties Task to also assign the generated filename to a variable, let call it SourceFileName.
In your ActiveX script, sSourceFile will now be assigned the value from that variable.
If you create a Global Variable SourceFileDate, then in your ActiveX script, assign the file date to that variable by using:
DTSGlobalVariables("SourceFileDate").Value = oFile.DateCreated
The global variable SourceFileDate will now have that date and you can pass it to other task via parameters.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
March 11, 2009 at 9:02 am
Alvin
that works perfectly!
many thanks for your help
March 11, 2009 at 9:03 am
Great and you're welcome.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply