Link to home
Start Free TrialLog in
Avatar of MohitPandit
MohitPanditFlag for India

asked on

SSIS: Copy data for few tables from different 3 servers into one database

Hello Folks,

I have three servers with identical database. So, I need to copy few tables (common from all server's databases) and store in one database only.

So, I shall follow below steps:

1. CREATE SSIS Package
2. CREATE Four database connections (for different server)
3. Data Flow Task for each database connection
4. Mapping of tables in Data Flow Task.

Is the correct and best approach OR do you have other alternatives?

Best Regards,
Mohit Pandit
Avatar of Steven Reid
Steven Reid
Flag of Australia image

Hi Mohit,

Some questions
- Are the tables on each source database called the same?
- Is the information in each source database to be added to the same table?
- Do you need to know which database it comes from?
- Will this overwrite the destination table?

Questions not withstanding, you could create a loop in SSIS to loop through the databases, but if there are only going to be the 3 source databases, it might be just as quick to do the way you mention.

and choose whether to do in parallel, or sequentially.

Thanks
Steve
Avatar of LajuanTaylor
LajuanTaylor

You can copy the tables using SQL Server Management Studio Tools. See attached .pdf instructions. Here's a link to instructions on how to install the tools and download them from Microsoft:
http://www.sqlshack.com/sql-server-management-studio-step-step-installation-guide/
CopyDataUsing-SQLServerManagementStudio.
Avatar of MohitPandit

ASKER

Hi Steve,

Please find below:

Some questions
 - Are the tables on each source database called the same?
[MP:] Yes
 - Is the information in each source database to be added to the same table?
[MP:] Yes
 - Do you need to know which database it comes from?
[MP:] Yes
 - Will this overwrite the destination table?
[MP:] No, it will append.

Do you have any reference link for looping?

Hi LajuanTaylor, I want to automate only with SSIS.

Thanks

Best Regards
ASKER CERTIFIED SOLUTION
Avatar of Vikas Garg
Vikas Garg
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Vikas,

I've 10 tables. Do you thoughts for multiple tables?

Best Regards
Do those 10 table have same structure and you want all data to a single destination database table ?

If yes then you can do this using for each loop and dynamic connection string for sources
Okay, let me try.

BTW, 10 tables have different columns

Best Regards
Thanks. I used execute sql task, for each container and script task to achieve this.