MSBI Incremental load in SSIS

Incremental load in SSIS

An incremental load is useful to check the target table against the source table based on timestamp. To keep data between two systems in sync with each other, we can use incremental loads in SSIS. It helps in cases where we need to load data into the destination regularly. SSIS provides features to easily perform incremental load with configuring a few steps. 

Why incremental load?

After a data import, if new data is added to the source table, then data inconsistency occurs. If we send the entire table data into the target table again, there will be data duplication. To avoid these data inconsistency problems, we need to send only the data that has been added after the initial import.Incremental import in SSIS helps to send only the newly added data. 

                          Become a SSIS Certified professional  by learning this HKR SSIS Training !

Advantages of incremental load

The following are the advantages of performing an incremental load of data.

• If we modify any records in the source table, we can reflect on the changes on the target table.
• It takes very little time to perform incremental load in the ETL process.
• If we have performed reporting on the target table, then we would need that data to be updated frequently.
• We can always maintain the data consistent across source and target.

Types of incremental load in SSIS

We can perform incremental load in SSIS in four ways.

Lookup Transformation:

It checks each record of the table in the source database against the lookup (target) table in the destination database. If a record is not found, it will be added to the target table. If a record is found, the data of the record in the target table will be replaced with that of the source table.

Using CDC (Change Data Capture):

The Change Data Capture captures inserts, updates, and deletes into changed sets. We can enable CDC on the source database table. This will use the CDC change set and apply the transfer of data based on it.

Using SCD (Slowly changing dimension):

It compares the incoming source table data with the existing destination table data using a unique parameter (primary key). It checks the records for new, changed, and unchanged conditions.  This method is best used for tables that are not frequently updated.

Using SQL task (Merge statement):

We can sort the data in both the source and target tables.We can then include the insert and update statements into a single merge statement. This will apply a full outer join based on a unique parameter (primary key).

               Become a MSBI Certified professional  by learning this HKR MSBI Training !

Sample table data

Let's take the below sample table as an example.

Let’s think that this table is already loaded to a target table in a destination database.Let’s add one more row to the source table that contains details like - 1239, Emily Edwards, Operations, Telangana. Now the table in the source database has 6 rows whereas the table in the target database has 5 rows.                                               

To view this source table data in SSIS,execute the below SQL query. 

Want to know more about SSIS, visit here SSIS Tutorial.                                                                                                                                                                                                                          

SELECT [EmployeeID],

       [Name],

       [Department],

       [Location]     

       FROM [HKR Trainings].[dbo].[]

would be the name of the table in your source database. You can see the below table in the Results pane.   

Let’s take a look at the table in the destination database. Execute the below SQL query.                                                                                                                                                                                                                                                                                                              

SELECT [EmployeeID]

             ,[Name]

             ,[Department]

             ,[Location]     

  FROM [HKR Trainings].[dbo].[]

would be the name of the table in your destination database. You can see the below table in the Results pane.