sql server dts: SQL 2K: Help needed with dynamic output file names -- sql server dts

SQL 2K: Help needed with dynamic output file names

Matthew Speed
3/30/2006 10:51:23 AM

sql server dts: I have been asked to produce a set of csv output files for a number of
subsets of data from a database. I have created a DTS package that
can output each query to a hardcoded filename specified in the
Destination file object in the package. As I have to do this
appoximately 160 times I need to know how to change the name of the
target file programmatically.

I am guessing this will use an ActiveX script within the package to
modify the target as the first action in the script. Can someone
point me in the right direction to learn to be able to modify a
property internal to the package by using an action in the package
itself?

Re: SQL 2K: Help needed with dynamic output file names

Bill Swartz
4/1/2006 11:03:14 PM

Matthew,

What I would do is use the dynamic properties task to manage the file name
for the Destination file.

So the flow would be.

Use ActiveX to set the file name and update a global variable.
DTSGlobalVariables("ExportFilename").Value = myNewFileName

Execute the DynamicPropertiesTask to set the DestinationFile name based on
the global variable.
Execute the export

Bill

[quoted text, click to view]