April 21, 2004 at 10:21 am
How we could create a DTS packet to pick the file by its creation date.
I need to have a packet running every night and to import all files created in the day - they are all with the different names
I would appreciate any idea or a link about.
Thanks a lot in advance.
MJ
April 21, 2004 at 3:57 pm
MJ;
April 22, 2004 at 5:51 am
You can use an ActiveX task with VBScript and the File System Object to look at the files' DateCreated property, get the file name and modify the import task's Source file to be the file name you picked out.
I don't have an example of specific code handy to show you, but if you do some searches on the forums here and look at some of the articles on http://www.sqldts.com you should be able to work it out.
April 22, 2004 at 6:03 am
I would approach this problem in different manner.
Using the method of your choice (batch file, VBS script, compiled program, manual labor, whatever it takes) make a copy of the latest file and save it with a filename that will be the same all the time.
Once you have a process for doing this that works every time, then call the DTS package that would use the copied file.
This approach will keep your DTS package a bit more simple in design. This is a helpful feature for others who will have to maintain the system in the future.
As for the process of finding and copying the latest file, I am sure there are many VBS or VB examples out there to do that.
Good luck,
-Al
"I will not be taken alive!" - S. Hussein
April 23, 2004 at 7:23 pm
Here's how in a two step DTS pkg:
SQL Task:
--Write contents of directory to file
Declare @CMD varchar(100)
set @cmd='Dir c:\ImportFolder > c:\ImportFolder\Dir.txt'
exec master.dbo.xp_cmdshell @CMD
ActiveX Task:
'Parse folder contents for files from today
Dim fso , f , s , ts, sFDate, oPkg
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.OpenTextFile("C:\ImportFolder\Dir.txt", 1)
Do While Not ts.AtEndOfStream
s = ts.ReadLine
If isdate(left(s, 10)) Then
If cdate(left(s, 10)) = Date and right(s,1)<>"." and _
right(s,7)<>"Dir.txt" Then
'ie Today's date, not a directory, and not the directory file
'Use method by SQLGutter - copy to standard import file name
fso.CopyFile rtrim(mid(s,39,100)), "c:\ImportFolder\ImportFile.txt"
SET oPkg = CreateObject("DTS.Package")
oPkg.LoadFromSQLServer "ServerName\Instance", "", "", "256", _
, , ,"My Package to process files every night"
'256 flag is for trusted connection
' pass in Global variable for name of file, for example:
oPkg.GLOBALVARIABLES.item("gvFilename").value=rtrim(mid(s,39,100))
oPkg.Execute
oPkg.Uninitialize()
Set oPkg=Nothing
End If
End If
Loop
set ts=nothing
set fso=nothing
Enjoy!
Bill
April 26, 2004 at 4:54 am
You can do it without the first task if you just use a couple more FSO properties and methods:
Dim fso , f , fs , fc , fn, fd , oPkg
If fd = Date Then
SET oPkg = CreateObject("DTS.Package")
, , ,"My Package to process files every night"
'256 flag is for trusted connection
' pass in Global variable for name of file, for example:
oPkg.GLOBALVARIABLES.item("gvFilename").value=fn
Set oPkg=Nothing
End If
set ts=nothing
set fso=nothing
April 27, 2004 at 2:54 pm
Thanks, rschaeferhig. That's much better. I knew there had to be a better way, but went with what I could think of at the time.
Here it is again with some tweaks after testing:
Dim fso , f , fs , fc , fn, fd, fm, oPkg
Set fso = CreateObject("Scripting.FileSystemObject")
Set fs = fso.GetFolder("C:\ImportFolder")
set fc = fs.Files
For each f in fc
fn = f.name
fd = f.datecreated
fm=f.datelastmodified
'msgbox fn&vbcrlf&fd&vbcrlf&fm 'For testing
If DateValue(fd) = Date or DateValue(fm)=Date Then
SET oPkg = CreateObject("DTS.Package")
oPkg.LoadFromSQLServer "ServerName\Instance", "", "", "256", _
, , ,"My Package to process files every night"
'256 flag is for trusted connection
' pass in Global variable for name of file, for example:
oPkg.GLOBALVARIABLES.item("gvFilename").value=fn
oPkg.Execute
oPkg.Uninitialize()
Set oPkg=Nothing
End If
Next
set ts=nothing
set fso=nothing
I added datelastmodified because from my brief testing it appeared that when a file was over-written, the datecreated was from the original over-written file while the datelastmodified was from when it was overwritten. On the other hand, if a new file was copied into the folder, its datecreated was when it was copied while its datelastmodified was from when the source file was last modified. Hence, depending on the circumstances, either could be correct.
Bill
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply