Merge 2 tables from different DB on Different Server into single table in destination DB

Question

Merge 2 tables from different DB on Different Server into single table in destination DB

Helpseeker

Right there with Babe

Points: 782
More actions
January 27, 2016 at 3:42 am

#307490

Hi,
Am new to SSIS. I have 2 different tables lying in 2 different DB on 2 different Servers.
I need to merge both the tables to single destination table which is present in another different server.
Is it possible through SSIS? If yes how can i achieve it?
Thanks in advance.

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

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

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

kavithaje (1/27/2016)
Hi,
Am new to SSIS. I have 2 different tables lying in 2 different DB on 2 different Servers.
I need to merge both the tables to single destination table which is present in another different server.
Is it possible through SSIS? If yes how can i achieve it?
Thanks in advance.

If the tables were in the same DB, would you be able to do the merge in T-SQL? If yes, the answer to your question is yes.

How complex the SSIS package needs to be depends a lot on your merge logic. Moving data from one server to another is trivial.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 2

Phil Parkin (1/27/2016)
kavithaje (1/27/2016)
Hi,
Am new to SSIS. I have 2 different tables lying in 2 different DB on 2 different Servers.
I need to merge both the tables to single destination table which is present in another different server.
Is it possible through SSIS? If yes how can i achieve it?
Thanks in advance.
If the tables were in the same DB, would you be able to do the merge in T-SQL? If yes, the answer to your question is yes.

Yes, it's possible. I'm doing it all the time.

Having the tables on different servers requires Linked servers creation.

Igor Micev,My blog: www.igormicev.com

Helpseeker Right there with Babe Points: 782 More actions · Answer 3

Thank you for the response.

Could you please let me knwo how to do it?

Phil Parkin SSC Guru Points: 246985 More actions · Answer 4

Igor Micev (1/27/2016)
Phil Parkin (1/27/2016)
kavithaje (1/27/2016)
Hi,
Am new to SSIS. I have 2 different tables lying in 2 different DB on 2 different Servers.
I need to merge both the tables to single destination table which is present in another different server.
Is it possible through SSIS? If yes how can i achieve it?
Thanks in advance.
If the tables were in the same DB, would you be able to do the merge in T-SQL? If yes, the answer to your question is yes.
Yes, it's possible. I'm doing it all the time.
Having the tables on different servers requires Linked servers creation.

Point #1: you do not need any linked servers. You will, however, need separate connections to all three servers in your SSIS package.

As I do not know what your merge logic is, here is one way which should definitely work. Based on your actual requirements, there may be some shortcuts.

Before doing anything with SSIS, create 2 staging tables in your target database.

Your package logic would be something like this:

1) Truncate staging tables

2) Data flow from source table 1 to staging table 1

3) Data flow from source table 2 to staging table 2

4) Execute a stored proc in the target database using an ExecuteSQL task.

The stored proc will perform the merge for you.

As I suggested, this is likely to be sub-optimal, because all of the data is being transferred all of the time. Steps 2 and 3 can possibly be refined to bring back only new and changed rows, so that this runs in 'incremental-' rather than 'full-' load mode.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Helpseeker Right there with Babe Points: 782 More actions · Answer 5

Thanks Phil. This is the great idea. i will follow your steps. 🙂

Helpseeker Right there with Babe Points: 782 More actions · Answer 6

Hi,

I know CDC is the best way to do incremental load from source to destination. But since my source DB is Read only and i do not have sufficient privilege to enable CDC, can i do incremental load just by using SSIS?

I saw it using Lookup we can do the incremental load but am not understanding how to achieve it. Can you help me?

Thanks in advance.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 7

kavithaje (1/28/2016)
Hi,
I know CDC is the best way to do incremental load from source to destination. But since my source DB is Read only and i do not have sufficient privilege to enable CDC, can i do incremental load just by using SSIS?
I saw it using Lookup we can do the incremental load but am not understanding how to achieve it. Can you help me?
Thanks in advance.

Do the tables in the source system contain sufficient data to allow you to check for new and changed rows?

Eg, DataCreated and DateModified columns

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Helpseeker Right there with Babe Points: 782 More actions · Answer 8

Hi phil,

yes i have sufficient data in source table and am able to use lookup and conditional split for incremental data load.

problem is i have huge number of records in my source table and everyday large number of rows gets changed. Since lookup checks for each and every rows with changes, does it affect the performance?

Since source is read only and it will be used for report purpose also, i dont want to increase the load on source and even incremental load to destination table also should be fast.

Is there a way to achieve that?

Thanks in advance.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 9

Yes there is.

Here is a design pattern for you. This assumes that you keep the date created and date modified columns from your source database in your target database too.

1) Truncate staging tables on target database

2) For each table:

a) Get max(DateCreated, DateModified) from target database table and put in variable MaxDate

b) Transfer from source database to staging table where DateCreated >= MaxDate or DateModified >= MaxDate

c) Merge from staging table to target table using ExecuteSQL task.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Helpseeker Right there with Babe Points: 782 More actions · Answer 10

Thank you Phil,

Is there any example to do that?

i have row version in my source table. using row version can i do the incremental load? will the performance is fast if i use row version?