How can I use MERGE statement across multiple data

Question

My Source and Destination tables exist on different servers. I am using Execute SQL Task to write Merge Statements to synchronize them.

Could anyone explain how I can reference two different databases that exist on different servers inside my Execute SQL Task?

Answer 1

See this link - http://technet.microsoft.com/en-us/library/cc280522%28v=sql.105%29.aspx

Basically, to do this, you would need to get the data from the different servers into the same place with Data Flow tasks, and then perform an Execute SQL task to do the merge.

The Merge and Merge Join SSIS Data Flow tasks don't look like they do what you want to do.

Answer 2

Possible approaches:

I would suggest the following approaches instead of trying to use MERGE statement within Execute SQL Task between two database servers.

Approach #1:

• Create two OLEDB Connection Managers to each of the SQL Server instances. For example, if you have two databases SourceDB and DestinationDB, you could create two connection managers named OLEDB_SourceDB and OLEDB_DestinationDB. You could also use ADO.NET connection manager, if you prefer that. Based on what I have read in SSIS based books, OLEDB performs better than ADO.NET connection manager.

• Drag and drop a Data Flow Task on the Control Flow tab.

• Within the Data Flow Task, configure an OLE DB Source to read the data from source database table.

• Use Lookup Transformation that checks whether if the data already exists in the destination table using the uniquer key between source and destination tables.

• If the source table row does not exist in the destination table, then insert the rows into destination table using OLE DB Destination

• If the source table row exists in the destination table, then insert the rows into a staging table on the destination database using another OLE DB Destination.

• Place an Execute SQL Task after the Data Flow Task on the Control Flow tab. Write a query that would update the data in destination table using the staging table data.

Check the answer to the below SO question for detailed steps.

How do I optimize Upsert (Update and Insert) operation within SSIS package?

Approach #2:

• Create two OLEDB Connection Managers to each of the SQL Server instances. For example, if you have two databases SourceDB and DestinationDB, you could create two connection managers named OLEDB_SourceDB and OLEDB_DestinationDB.

• Drag and drop a Data Flow Task on the Control Flow tab.

• Within the Data Flow Task, configure an OLE DB Source to read the data from source database table and insert into a staging table using OLE DB Destination.

• Place an Execute SQL Task after the Data Flow Task on the Control Flow tab. Write a query that would use the MERGE statement between staging table and the destination table.