CODEWITHSUNDEEP
sql-serverssis
Alexander Galkin
Alexander Galkin

Reputation: 12524

Transfer several joined tables from one database to another using SSIS?

I have several tables in my database A which are interconnected via foreign keys and contain values. These values need to be transfered to another database B, all dependencies must be preserved, but the actual (numeric) values of primary and foreign keys are, of course, of no importance.

What would be the easiest way to fulfill this task using SSIS?

Here are the approaches I tried but with no much success:

  1. I implemented a really very sophisticated view with flattened data and a lot of redundancy in the data and bumped into the problem how to split the data from this flattened view into several tables connected via foreign keys. This might be a solution, but I would personally prefer to avoid the data flatenning step if possible.

  2. I tried to copy the tables one-to-one using NOCHECK options to lift up the constraint checks and to perform insertion into PK and FK fields. This, however, confines my transfer to a complete new import, I cannot just "add" some new data to existing set of data that would be nice.

Any other suggestions?

Upvotes: 2

Views: 1426

Answers (1)

Rednaxel
Rednaxel

Reputation: 968

Integration Services has a Control Flow called Transfer Database Task and Transfer SQL Server Objects Task exclusive for what you need.

Here is a tutorial for what you need LINK.

Upvotes: 2

Related Questions