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)?

  • What is the performance penalty of XML data type in SQL Server when compared to NVARCHAR(MAX)?
  • .NET Regex for SQL Server string… but not Unicode string?
  • How to protect SqlLocalDB database file with custom password
  • Writing data to text file using BCP prompts for file storage type
  • How to update a SQL table column with XML data
  • Calling SQL Server database from Unix
  • 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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.