MohitPandit
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
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
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.
http://www.sqlshack.com/sql-server-management-studio-step-step-installation-guide/
CopyDataUsing-SQLServerManagementStudio.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Vikas,
I've 10 tables. Do you thoughts for multiple tables?
Best Regards
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
If yes then you can do this using for each loop and dynamic connection string for sources
ASKER
Okay, let me try.
BTW, 10 tables have different columns
Best Regards
BTW, 10 tables have different columns
Best Regards
ASKER
Thanks. I used execute sql task, for each container and script task to achieve this.
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