How to synchronize data from source and destination in SSIS

Problem: How to synchronize data from source and destination. When there is a updated row or new row inserted in the source table, the destination should also get the same information using SSIS.

Solution: Lookup Transformation

In SSIS, The Lookup transformation performs lookups by joining data in input columns with columns in a reference dataset. You use the lookup to access additional information in a related table that is based on values in common columns.

The reference dataset can be a cache file, an existing table or view, a new table, or the result of an SQL query. The Lookup transformation uses either an OLE DB connection manager or a Cache connection manager to connect to the reference dataset.

Example:

In this article, I am going to show we can use look up transformation to synchronize data between two tables.

Step 1: go to the control flow, drag and drop a dataflow task to the control flow.

Step 2: add an ole db source to reference the source of the dataset and ole db destination to check for the source changes. To check the changes, we need do to a lookup on the columns for new rows or updated rows.

Step 3: from the lookup transformation map the rows to the destination to send no match output to the destination.

Execute the package.

 

OUTPUT:

Col1	Col2	Col3	col4
11	1	1	agad
12	22	22	aag
13	1	21	gaagadga
14	34	31	NtestULL