Pass parameter(s) to SSIS import package.

[joejack0330]

We'd like to have an sp that runs nightly that would loop through a query of customers and kick off an ssis import package with the text file path and name being based on that customer. They will all have same import file layout but could be different names and coming from different folders based on customer. We could have 100 different and don't want to have 100 separate packages to maintain. We might want to also output a file for customer too. Is there a way to do this?

Thanks,
Joe

 

[MDXer]

I would have a table in a with the relevant information and then have an enabled flag. In the package I would then read in the information for the records that are enabled. I have a similiar process now that does things this exact way.

if you want to pass params in using dtsx you would

/SET ""\Package.Variables[VariableName].Value";-1

 

[joejack0330]

Thanks for the help, it pointed me in the right direction. I think I got the basic understanding, now working the foreach loops. Pretty cool stuff.

 

[MDXer]

once you get the basics of the for each loop task they become second nature, and pretty powerful especially when dealing with large data volumes.