Executing an SSIS DataFlow task with different variable value

Question

Hi,

I need to query three different database and dump them into csv files. Its the same procedure for the three databases. The only difference is the database and the name of the csv file. Can I do this without cutting and pasting? Is there a way to pass parameters to the data flow task?

Thanks!

Answer 1

Your flat file and db connection managers could have the connection string based on a package scoped variable.

Then use a foreach looping container to call your dataflow task. Configure the looping container with a foreach item enumerator and add the appropriate names to the collection.

Answer 2

santiiiii's explanation covers the use case of downloading the data in one package execution. If you need to get the data at different times, then you can use a conditional statement in a variable that will give you different file names and database connections based on the supplied value for the variable. You can then set the value of the variable in the SQL Server Agent Job in the Set Values tab. This can give you more flexibility, but santiiiii's solution is definately best if you want to process all three files at the same time.