DTS to TEXT File
-
I have a need to create a text file exported by a DTS package. The problem is that the newly created text file must include a day and time in the file name. I know I can do this manually - however, I would like to write a DTS package where it can create the new text file with the day and time concatenated in the file name each time it runs.
-
Ray, you could try something like this.
sOutString = Day(Now) & Hour(NOW) & Minute(Now) & Second(Now)
and then append sOutString as part of the file name
-
Where would I place that statement? And, how would I append it to the filename? -
Here's a sample of how we do this:
sFileName="FileName-" & Right("0" & DatePart("m", Date), 2) & _
Right("0" & DatePart("d", Date), 2) & _
Right(DatePart("yyyy", Date), 2) & ".txt"
DTSGlobalVariables("FileName").Value=sFileName
DTSGlobalVariables("FilePathName").Value="<<FilePath>>" & sFileName
You can use that as a basis for adding the minutes and seconds. Put in in an ActiveX script , then use the update dynamic properties step to alter the properties of the output file.
-
You can accomplish this task in two ways.
1) Create the same text file every time and use FSO to rename the file to include the date/time in the name.
2) Dynamically change the file name of the connection to reflect the date/time using the custom object...
Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Source)")
oConn.DataSource = sFilename
where sFilename is the new filename that includes the date/time in the name.
Check out http://www.sqldts.com for examples.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply