Hi, I have 4 tables in 4 databases. i want load these tables data into destination table.here meta data is same in all tables. what is the best approch. plz let me know

Hi, 1) Basically a Union All transformation will arrange it in a single Data Flow Task (four data sources, a Union All transformation, and a destination) 2) You can put a Data Flow Task into a For Loop Container (one parameterized data source and one destination) and let it loop through the four tables. 3) You can put four separate Data Flow Tasks (one data source, one destination each) onto a single package. 1 and 3 will keep your resource utilization high (you have to decide if it's good or not). But 2 is better in case you need to spare some CPU for other applications, or you would like to have a lower chance of locking the source tables. I would start with no. 1 first, monitoring the performance meanwhile. If there are no serious issues, I would leave it as it is. Kind regards, Zoli-- Zoltn Horvth -- MCITP SQL Server Business Intelligence Developer 2005, 2008 -- MCITP Database Developer 2008 -- Please mark posts as answered or helpful where appropriate.

Are these databases on the same server instance? If so, you can probably avoid SSIS altogether and just use T-SQL to UNION ALL the data with a standard INSERT. If they are on different servers, there are various approaches - but the simplest may be to just use a For Each container to loop through the servers/databases and insert the data into your table with a dataflow. Something similar to this: http://www.codeproject.com/KB/database/foreachadossis.aspxevery day is a school day My Blog

Hi, Zoli can u explain 2 option clearly. thats very help full for me.

Hi, Check the link given by richbrownesq - it solves the same problem with the approach 2, however it uses For Each Loop Container. Since your tables are from separate databases, you can use that tutorial - only one thing is missing from that: the name of the databases should be stored as well. Regards, Zoli-- Zoltn Horvth -- MCITP SQL Server Business Intelligence Developer 2005, 2008 -- MCITP Database Developer 2008 -- Please mark posts as answered or helpful where appropriate.

Hi, You can write the SQL Query for insert by using union and in SSIS you can use Execute SQL task instead of dataflow for Insertion. Thanks, Shobhit

Hi Zoltán Horváth, I gone through the link, here i have small dout overhere . 1) create table for databases name . fetch the database names into recored set from table. how can i dynamically get table names, respect to particular data bases. like DB-->TABLE A DB2-> TABLE B DB--> TABLE C HERE i get the data base names . how can i get table names dynamically my work flow is EXECUTE SQL TASK---> FOR EACH LOOP --> DFT-> SOURSE--> DESTINATION

Hi, Create a new string variable in the SSIS package called TableName. Create a new column called TableName in the dbo.Server table. Fill this column up with the corresponding table name (i.e, TABLE A for DB, TABLE B for DB2, etc.). Other changes: On the 2nd picture, enter "select ConnectionString, TableName from dbo.Server" instead. On the 5th picture of the tutorial, map the User::TableName variable to Index 1 as an additional row. After picture 8, set up an expression for the SQLStatementSource property of the OLE DB Source component like "SELECT * FROM " + @[User::TableName]. It should work then. Kind regards, Zoli-- Zoltn Horvth -- MCITP SQL Server Business Intelligence Developer 2005, 2008 -- MCITP Database Developer 2008, Database Administrator 2008 -- Please mark posts as answered or helpful where appropriate.