automate importing txt files through dts into a sql db
Posted on 2004-04-07
I need to automate importing a daily txt file into a sql db using dts. I used the File Transfer protocol task to copy the files from a ftp site to a local directory. Daily I only need to import the latest file into the db. The txt files are like this in the directory.
Today is the 20040407, so the latest file I would import would be dailye20040406. If it's a Monday I would go back to the Friday's date.
I can do the above manually in dts, but need to automate the process. I have been playing around with the active x task to do this, but am not experinced with the active x language. I got this far - so far I can only see if the file exists.
Dim objFSO, strFullNm
Set objFSO = CreateObject("Scripting.FileSystemObject")
StrFullNm = "c:\FSOExamples\daily_edf_NF_NC_NB" & CStr(Year(Now)) & "0"&CStr(Month(Now)) & CStr(Day(Now)) & ".txt"
If objFSO.FileExists(strFullNm) Then
MsgBox "The File Exists"
MsgBox "You still need to create the file"
Set objFSO = nothing
Main = DTSTaskExecResult_Success