Changing Export Text File Names

Question

Changing Export Text File Names

tblack

SSC Rookie

Points: 27
More actions
January 29, 2003 at 6:22 am

#105992

Need help, would like to alter the text file name in an export DTS package. We would like to add the current date so that would look something like the following example.
AOR-CASE01292003.txt
If anyone has done this and has an example, and would be willing to post it, that would be greatly appreciated.
Thanks in advance for the help.
TBlack

Thanks TBlack

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

cafemar1 SSCrazy Points: 2405 More actions · Answer 1

You need to use an ActiveX step to create the file within your DTS. I have done it, but don;t have the sample with me at this time. I will try to find it, if so I will post the sample I used.

In any event you will need to create a function and use CreateObject with the specifications you need 'AOR-CASE+''+cast (getdate() <set the date format you need>

No promises, but I will try to find it. If not I hope this can give you a clue in how to proceed. Good luck!

tblack SSC Rookie Points: 27 More actions · Answer 2

I have a vb script attached here -

Dim oConn, sFilename

' Filename format - exyymmdd.log

sFilename = "AOR-CASE-IOWA" & Right(Year(Now()), 2)

If Month(Now()) < 10 Then sFilename = sFilename & "0" & _

Month(Now()) Else sFilename = sFilename & Month(Now())

If Day(Now()) < 10 Then sFilename = sFilename & _

"0" & Day(Now()) Else sFilename = sFilename & Day(Now())

sFilename = DTSGlobalVariables("LogFilePath").Value & _

sFilename & ".log"

Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Source)")

oConn.DataSource = sFilename

Set oConn = Nothing

Main = DTSTaskExecResult_Success

I have not been able to make this work, I get the following error message everytime I attempt to execute the package.

'Connection 'Text File (Source)' was not found'

what I can not figure out is where in the package should this step be.

DB Connection - SQL task - Text Destination.

Thanks TBlack

philcart SSC-Forever Points: 47794 More actions · Answer 3

Are you sure you have the name of the connection right?

The line

Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Source)")

is looking for a connection called "Text File (Source)". BTW, if this is a file that data is being written to, you probably need the "Text File (Destination)" connection.

Also, try this to build your filename,

sFilename = "AOR-CASE-IOWA" &_

Right(Year(Date()), 2) &_

Right("0" & Month(Date()), 2) &_

Right("0" & Day(Date()), 2)

A bit simpler, uses the Date() function instead of Now() because Now() returns date and time.

Thanks

Phill Carter

--------------------
Colt 45 - the original point and click interface

tblack SSC Rookie Points: 27 More actions · Answer 4

Thanks for the help, I figured out that it was referencing the Source file not the destination file, Once I made the change everything worked. Thanks again for everyones help with this.

Thanks TBlack