Dynamically set name of DTS output file (text file)

[StevenK]

Can a DTS package output to a text file that is named dependent on the date / time it was run on?
So one moment when I run the DTS package - it generates a text file called 'TextFile_20060731_1305' say and 10 minutes later might call the resultant file 'TextFile_20060731_1310'.

How can this be done?
Thanks in advance.
Steve

 

[philhege]

Text output target names are static, I believe. Have your DTS package call a script that copies the target and renames the copy after the export is done.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[rac2]

Search this forum on dynamic file name. I found the answer here, but I cant remember who or when.

1. Set up the DTS with a fixed file name.
2. Add a Dynamic Properties Task. The property to be dynamically set is Connections->TextFile(Destination). The Property Name is Data Source.
3. Set Data Source to be Source: Query;
Connection is Microsoft OLE DB Provider for SQL Server
Query would be like the following

Declare @vcharFileName as varchar(100)
Declare @vcharFolder as varchar(100)
SET  @vcharFolder = 'C:\WINDOWS\Desktop\' -- Change to folder containing files

SELECT 
@vcharFolder +
'some_stuff' + 
Convert(varchar,DatePart(yy,GetDate())) +
Right('0' + Convert(varchar,DatePart(m,GetDate())),2) +
Right('0' + Convert(varchar,DatePart(d,GetDate())),2) +
'.txt'  as 'File_Name'

Click Refresh, click OK, OK, OK, etc.

4. Add a Workflow between the Dynamic Properties Task and the SQL Server connection.

This last may seem stupid because we are changing the text file name. It did to me. But I guess the reason is that the dynamic widget is using SQL Server, that is where queries work. And the actual thing being changed was specified inside the widget. Anyways it works.

 

[mrdenny]

You need the workflow to ensure that the filename is changed before the data transformation in completed and not after.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com