Hi , really i am new to SSIS and i am wondering how i can transfer 10 database data to one database all 10 database have the same structure also the destination database and every database has more than 20 table and all source and destination databases in the same server. in some tables there are duplicated rows but mostly no duplicated rows. the database in SQL 2000 but I will use SSIS 2008 R2. Is there any way i can do something like this in SSIS ? sorry if i am posting an answered question cos really i am stuck in this issue or i will do it one by one using import/export. Regard,

You can create 10 db connections (OLEDB provider) and using Data Flow and OLEDB Source and OLEDB Destination transfer the dataBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

it would be difficult to create 10 connections, do we can use variables ? and what about the tables ? Regards,

it would be difficult to create 10 connections, do we can use variables ? and what about the tables ? Regards, You don't have to create 10 connections. You have to setup one source connection that you will change dynamically and one destination connection, which will be static. Then you have to setup For Each Loop container, which will iterate over your source databases and then you can pull data using one or more data flow tasks.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/