Loading Data of 300 tables from one sql server to another sql server using SSIS

Question

Loading Data of 300 tables from one sql server to another sql server using SSIS

Samit Shah

Mr or Mrs. 500

Points: 510
More actions
February 2, 2012 at 12:00 pm

#254736

Hi,
I am trying to load the data from one sql server to another sql server using SSIS. I am aware about doing it with DataFlow task. But there are 300 odd tables to be loaded. This will be weekly load, so I have to load new rows and update the existing rows for each table. Does anyone has solution for it. Thanks in Advance
Samit Shah

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 1

Have you thought about Replication to do what you want to achieve? If not, I would recommend you to explore that possibility. Though it is too generic of an answer, as there is not much of information about the underlying tables..

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

Samit Shah Mr or Mrs. 500 Points: 510 More actions · Answer 2

We have requirement to do it through SSIS, since we need to log inserted, updated, deleted records.

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 3

Well, if there requirements need you to record what's moved on what date and time, you will have to come up with SSIS packages (Replication can't do that), along with additional columns to identify rows uniquely and any status flags etc etc..

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

Samit Shah Mr or Mrs. 500 Points: 510 More actions · Answer 4

Samit Shah

Mr or Mrs. 500

Points: 510

February 2, 2012 at 3:22 pm

#1442122

Thanks Bru.

Any suggestions on how I can do it?

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 5

Well, it is not going to be easy without any knowledge on the tables structure (table description) and what is the criteria to know if an existing record is updated, and when was it updated. This is the starting point where I would start planning..

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

This article is a good starting point:

SSIS Design Pattern - Incremental Loads

Also take a look at the TSQL Merge statement if you are using SQL 2008 and up.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Samit Shah Mr or Mrs. 500 Points: 510 More actions · Answer 7

Thanks Koen and Bru.

I have already created incremental load for 1 table using merge join. I am thinking to make it generic, so that I dont have to write dataflow for all other tables, as there are around 300 tables. It is like copying the data from one database to another and both the database are on different servers. Do you have any suggestions for it.

Thanks and Regards,

Samit Shah

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 8

Samit Shah (2/3/2012)
Thanks Koen and Bru.
I have already created incremental load for 1 table using merge join. I am thinking to make it generic, so that I dont have to write dataflow for all other tables, as there are around 300 tables. It is like copying the data from one database to another and both the database are on different servers. Do you have any suggestions for it.
Thanks and Regards,
Samit Shah

SQL 2005 or 2008?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

thansen 28694 SSC Rookie Points: 25 More actions · Answer 9

Create a table that has a column of table names that you are wanting to update. Then add a SQL task to pull those rows, hook that into a ForEachLoop and using variables you can change the table name dynamically. Inside of your ForEachLoop you can do a script task that can then dynamically do the inserts, updates, etc. to accomplish what you need. In fact, in your same table of table names you could add the correct SQL statement and use that in the script to do the work you need to.