SSIS package – loop through different connections for Execute SQL task
I am creating an MSSQL2008 SSIS package to generate and email reports from database tables. It works perfectly on a single database. The client is running 3 different databases used by 3 different divisions. The database structure is exactly the same. All three databases are located on the same server, same security / credentials are used.
I created a “For Each Loop Container” in my SSIS package that loops through the list of 3 items and populates it into a variable. How do I now take that and pass it to the “Execute SQL Task” to run three times (once for each database)?
Thank you for your time!
One Solution collect form web for “SSIS package – loop through different connections for Execute SQL task”
It was a lot easier than I expected.
I went to Properties of the “Execute SQL Task” and under “Expressions” for “Connection” I specified @varDBName, which was the variable I populated in the outer “for each” loop. I also needed to set “DelayValidation” property to “True” so it’s only evaluated during run-time.
I hope this helps somebody else.