Transfer data between two different servers using SSIS

Question

I'm trying to create a SSIS package which will copy data from Table-A on Server-A into Table-B on Server-B. And to avoid duplicates, I want to update the data of the records which already exist in Table-B if there are any changes to the data. Please let me know what would be the best approach for this.

Thank You

Answer 1

Create regular data flow with 2 components - OLE DB Source and OLE DB Destination (I assume you are using MS SQL Server, in general, use whatever components your company uses to connect to the DB).

In case of 2 DBs, create 2 connection managers, each pointing to its DB. Point OLE DB Source to first connection manager configured to point to source of data, and OLE DB Destination to second connection manager configured to point to destination DB.

Now point OLE DB Source to the source table in source DB, leave all the fields intact. Connect source and destination components with green arrow originally going out of source component. Now point OLE DB Destination to the destination table in target DB. Double-click destination, go to mappings and make sure they are correct (SSIS tries to map automatically using strick name matching), otherwise (in case names are different) connect source and destination fields manually. That's it, you just don't provide mappings for the fields which cannot be accommodated by destination table.

Alternatively, you can leave out the columns you don't need at source component - double-click it, go to Columns and uncheck columns you don't need.

Answer 2

You should use the SSIS Sort Transformation to remove duplicate records

Drag Sort Transformation and Connect Flat File Source to it. Double-Click on Sort Transformation and Choose the columns to sort. Also Check the Checkbox : Remove rows with duplicate sort values and then click OK

The SSIS Sort Transformation task is useful when you need to sort data into a certain sort order.