SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

reidkell
Starting Member

USA
15 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.

visakh16
Very Important crosS Applying yaK Herder

India
52325 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
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.08 seconds. Powered By: Snitz Forums 2000