Hi, I have to get data from 70 odd databases on one server to 1 database on other server. Each of the 70 databases has 25 odd tables. All the 70 databases has the same 25 tables. I want to achieve this in one package. Here is the approach that i have taken. Please suggest me if there is an other way, so that the package runs even faster I am having 7 foreach loop containers in parallel and each loop has a Dataflow Task. In the dataflow task i am mapping 25 source tables to 25 destination tables. Iam adding a derived column in between. Each time the loop runs it changes the source database name and gets data from that database to target database, so each loop is taking care of around 10 databases:example 1st loop for 1-10, 2nd for 10-20. the 7 loops will iterate for 10 times each to get data for 70 databases. All the 7 loops are parallel and are not connected to each other. I am look for performance boost also.

Performance may be good with running 7 simultaneous tasks (loops) but maintainability may be poor. What happens when you need to change ONE thing about the Data Flow? You need to do it 7 times. So its a trade-off. Performance or maintainability. And YOU need to answer that based on volumes of data and number of times you expect to make changes.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

why did you created 7 foreach loop? and duplicate data flow task there? in this implementation if you want to change something in one data flow you should apply it in all data flows. you can create a package which has only one data flow who is responsible for 25 source tables to 25 destination. then you can use execute package task inside a parent package withing a foreach loop as many as you need.http://www.rad.pasfu.com