SSIS Loop a list of connection strings in a master package, executing two other packages for each connection string

1
votes

I am trying to figure out how to loop a list of connection strings in an SSIS package. MSDN suggests I can have an Execute SQL task generate a result set into a variable and use a For Each task to loop the results, but it's none too clear to me exactly how to create this.

So I have a table with a single nvarchar column with a list of Sql Server OLEDB Connection Strings in it, all that actually differs between them is the database name.

I want to iterate that list in the main package executing two other packages for each connection:

Foreach connstring Pass connstring to package 1 and execute it Pass connstring to package 2 and execute it next connstring

I am trying to use the ForEachLoop task in a SSIS Project in visual studio and the ExecutePackage task but the ForEachLoop task properties and Expressions I am finding hard to make sense of at all.

EDIT:

Ok I have figured out how to set the connection manager ConnectionString property to an expression based on a variable. Create a string variable, then select the connection in connection manager and in the properties window click the elipses for the Expressions property and select the ConnectionString property in the dialog, then concatenate the database name variable into the connection string in the expression editor.

All I need now is to figure out how to set another variable to be a string array of database names, that are configurable in the dtconfig XML file.

EDIT:

  • anyone know how to set an SSIS Package variable to be a string array read in from the dtConfig XML file for the package?
1
sql-serversql-server-2008ssispackages

1 Answers

1
votes

What I did in the end:

  • Create a Execute Sql Task
  • Set the SQL Statement: SELECT DatabaseName FROM tbDataBaseNames
  • Set the ResultSet property to Full result set
  • In the Result Set tab set Result Name to 0 and select my System.Object variable (this will end up holding an ADO Recordset)
  • IN the ForEachLoop container choose the Foreach ADO Enumerator
  • Select my System.Object variable as the ADO Object Source
  • In the Variable Mappings tab select my String variable (This will receive the value read from each row as it loops)