export with dts packets table to txt, now datetime as filename ?

Question

export with dts packets table to txt, now datetime as filename ?

markuss21

Old Hand

Points: 340
More actions
January 17, 2006 at 12:39 am

#364761

hi !
I have a dts packet and I start that every day on the same time. Now I want that the file name is included the datetime now. For example:
Packet_060117.txt or Packet_060117.xls

How can I make that ?

Thanks for help,
Markus

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

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

David.Poole SSC Guru Points: 75991 More actions · Answer 1

I have a similar requirement for a date changed Excel file. There are several ways of doing it but I put an ActiveX task as the first step of my DTS with the following code in it.

'**********************************************************************

' Visual Basic ActiveX Script

'************************************************************************

Function Main()

dim pkg

dim cus

dim targetFileName

targetFileName="C:\Documents and Settings\d poole\My Documents\Internet" & Year(Date())

' Ensure that months are always in 2 digit format.

if Month(Date())<10 then

targetFileName = targetFileName & "0"

End If

targetFileName = targetFileName & Month(Date())

' Ensure that days are always in 2 digit format.

If Day(Date())<10 then

targetFileName = targetFileName & "0"

End If

targetFileName = targetFileName & Day(Date()) & ".xls"

' Change the connection name to suit your own implementation

set pkg=DTSGlobalVariables.Parent

set cus=pkg.Connections("Connection 2")

cus.Datasource=targetFileName

'If you create objects then remember to destroy them.

set cus=nothing

set pkg=nothing

Main = DTSTaskExecResult_Success

End Function

All it does is change the DataSource property of the Excel connection to a filename with todays date in YYYYMMDD format.

You should be able to do something similar by substituting in the name of your text connection.

LinkedIn Profile

markuss21 Old Hand Points: 340 More actions · Answer 2

Hey thanks,

but where have I put it in ? I don't know 🙁

David.Poole SSC Guru Points: 75991 More actions · Answer 3

Drag the ActiveX Script task onto your design pane then double-click it.

Copy and paste the code from the post into the resulting code panel and save it.

Select the ActiveX task followed by the source connection. Choose OnSuccess from the task list and a green stripey line will be drawn between the two.

LinkedIn Profile

markuss21 Old Hand Points: 340 More actions · Answer 4

Have I connet the activeX to the output txt/xls object or to the database ?

David.Poole SSC Guru Points: 75991 More actions · Answer 5

Provided it precedes the tranformation into your txt/xls object it doesn't matter though personally I make it the first task in the DTS.

LinkedIn Profile