Hi there.

I have a scheduled DTS package set up on my SQL Server with the following
properties:

Source: a stored procedure which can take a parameter
Destination: a csv file on disk with a dynamic filename (each time the task
is run, a new filename is generated from an SQL query using getdate())

At the moment, no parameter is passed to the stored proc and only one file
per day is output. I would like however, to pass a parameter @MyType which
has a range of values - and to generate a new file for each value.

eg: The task is scheduled daily. For each Day, I'd like the following to
happen:

source destination
EXECUTE p_GetResults @Mytype = 1 1.csv
EXECUTE p_GetResults @Mytype = 2 2.csv
EXECUTE p_GetResults @Mytype = 3 3.csv
..
..
Note: The values for @MyType are stored in a database table so a cursor
query would be involved somewhere....

My question (finally!) is: Is it possible to create this loop by editing my
existing DTS package or will I have to create a new package to handle each
value for @MyType (which would be tricky as I need to query a table for those
values..)

I know this could be a trick one - any help would appreciated though!

You should be able to do this using a combination of a few of our
articles


How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)

Looping, Importing and Archiving
(http://www.sqldts.com/default.aspx?246)

How can I change the filename for a text file connection?
(http://www.sqldts.com/default.aspx?200)




"len" <len (AT) discussions (DOT) microsoft.com> wrote