Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Run same SSIS sequence on multiple connections
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

16 Posts

Posted - 10/02/2013 :  18:44:20  Show Profile  Reply with Quote
Hi, all. I have a SQL 2008 SSIS import process in a Sequence Container that needs to be executed three times (more may come online in the future), once for each network data collector. In other words, each week I need to import from Conn1, Conn2 and Conn3, performing the same process on each as the databases are identical, but the server names and credentials are not.

What is the best way to loop this process X number of times (three currently), using a different connection manager for each? I could create linked servers and do this within a stored proc, but would like to implement within my SSIS flow. Any info appreciated.

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 10/04/2013 :  03:18:17  Show Profile  Reply with Quote
you just need to add a For Each Loop container and move your sequence container within it. Then create a table to contain the database connection details to iterate in SSIS. Inside SSIS first add a execute sql task to load the details from table to an Object type variable. In For Each Loop use ADO.Net enumerator and point to the object variable created in first step to make it iterate for various database values stored. You'll also need variables created for using inside For Loop to get individual connection values out during each iteration. You can use the expression builder to assign the values for connections dynamically from variables which are created inside for each loop to get values for each iteration.

SQL Server MVP
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000