Incremental Load using Loopup Transformation in SSIS

As we discussed in the previous article how we can use the lookup transformation to check the source data and we can split the matching and unmatching records.

In the below example we can see how we can see simple incremental data load for student detail ( Std_id, Std_name ).
Source Table: studentDetails and
Target Table : StagestudentDetails

Here we need to load data from Source Table to target table and need to implement the incremental load and also need to remove the duplicate values from the source as well.

Have look at the below Package design. as discussed in the previous article by using sort transformation we can remove the duplicate and then data will copy by using Multicast transformation.

After lookup transformation in splitting the data which are matched and no matched.

Have look at this article to understand how we can remove the duplicate values from the source.

Drag and drop the Lookup transformation from the SSIS tool, and then go to Loopup Transformation Editor properties.

Select your lookup table and select which column you want to lookup with our Source data.

Drag and drop the Std_id as you Lookup column with source data and click ok.

Drag and drop two Multicase transformations for Matching and unMatching reports, have look at the above image.

Now you need to connect from Lookup transformation to Multicast Transformation using precedence constraints, once you have connected you will get on popup like below.

Select “Lookup Match Output” option from the Output dropdown. by selecting this option we are moving the which are records matched with source data.

Now again you need to join with another precedence constraint to another Multicast transformation which is no matching record.

Now we are ready with our data and need to our Target Table. here drag and drop two OLD designation transformations. Have look at the above image.

Now select your target table in OLE Destination and then do column mapping as well. Please have look at the below images.

Now Executed the Package and see the result. if you placed everything correctly as mentioned the package will be executed successfully. Have look at the below image.

Finial Result:

Thank You.!!

Leave a Reply

Prabhakaran Jayaraman