Incremental Load in SSIS

In this article, we will show you how to perform Incremental Load in SSIS with example. SSIS Incremental Load means comparing the target table against the source data based on Id or Date Stamp or Time Stamp.

• If there are any New records in Source data, then we have to insert those records in the target table. For example, every day, we have to insert the sales branch wise.
• If there are any updated values in Source data, then we have to update those records in the target table. For example, update the customer address.

In this example, we show the steps involved in configuring the Incremental Load in SSIS. Before we start creating the package, Let us see the Source data

Now let’s see the data inside the target table as well.

Here our task is to load the extra 4 records into target table and update data present in 2, 5, 10 rows using SSIS incremental load.

Configure Incremental Load in SSIS

STEP 1: Drag and drop the Data Flow Task from the toolbox to the control flow region and rename it as an Incremental Load in SSIS.

Double click on it will open the SSIS data flow tab.

STEP 2: Drag and drop OLE DB Source to the data flow region. Double click on it will open the OLE DB connection manager settings.

From the below screenshot, you can observe that We selected the following Database as a source database and [CDC Source] as a source table

STEP 3: Click on the columns tab to verify the columns. In this tab, we can uncheck the unwanted columns.

Click OK to close the OLE DB source and Drag the Lookup Transformation from the toolbox to the Data Flow tab.

STEP 4: Double click on the Lookup Transformation will open the Lookup Transformation Editor to configure the lookup table (reference table). Within the General Tab, under “Specify how to handle rows with no matching entries”, Change the default Fail Component to Ignore Failure option.

TIP: Please refer Lookup article to understand the properties present in Lookup and Lookup Transformation article to understand the configuration settings

STEP 5: Click on the connection Tab to configure the lookup connections settings. If you already created the OLE DB connection Manager, select from the list. Otherwise, click the New button and configure it. Here we selected the below Database.

Next, we are using the Query as the lookup table because we require only one column to lookup (i.e., Employee_ID), so we are using the query.

NOTE: In general, the requirement may include either ID or Date and Time

STEP 6: Click on the Available Input columns and drag to the available lookup columns to create a join between the 2 data sets. Employee_ID column in the source data will check against the [Employee_ID] column in the lookup table (Nothing but target table).

STEP 7: Drag the Conditional Split Transformation and connect the Lookup Match Output as the source to Conditional split

STEP 8: Double-click on it to provide the conditions. The condition we used in this transformations is ISNULL (LKP_Employee_ID). It means conditional split will check whether the lookup employee ID is Null or Not

• If the LKP_Employee_ID is Null, it means no record exists in the destination table. So we want to insert the new record.
• If the LKP_Employee_ID is not Null, it means there is already a record that exists in the destination table. So we want to update the existing data with new data.

From the above, you can see that we used 1 condition and one default output in Conditional Split Transformation. So, We get two outputs in total.

STEP 9: Drag and drop one OLE DB Destination and OLE DB Command Transformation on to the data flow region and then drag the Output Arrow to this new OLE DB Destination. A popup window called Input Output Selection will appear prompting you to select the Input-Output type: either INSERT or UPDATE.

NOTE: To update table data, we need the SQL Query to perform the UPDATE Statement. That’s why we are using the OLE DB Command Transformation

STEP 10: Double click on OLE DB Destination to select the OLE DB Connection Manager and select [CDC Target] table present in the Database to insert the new records.

STEP 11: Click on the Mappings tab to check whether the source columns exactly mapped to the destination columns. If you observe the below screenshot, we are not mapping for the Employee_ID column because it is the Auto-increment Identity column.

STEP 12: Double click on the OLE DB Command Transformation will open the Advanced Editor for OLE-DB Command to configure it. Within the Connection Managers tab, we are selecting the already created OLE DB Connection from the list.

Within the Component Properties, we have to write the SQL Statement to perform updates. Please select the SqlCommand property and click on the browse button (… button). Once you click on the browse button, a String Value Editor window will open to write the Statement. In this example, we are Updating the values in the products table.

The Statement we used in the above screenshot is

UPDATE [CDC Target]
   SET [FirstName] = ?
      ,[LastName] = ?
      ,[Education] = ?
      ,[Occupation] = ?
      ,[YearlyIncome] = ?
      ,[Sales] = ?
 WHERE Employee_ID = ?

Within the Column Mapping, we have to assign the appropriate column name to the parameters.

TIP: First Question mark will be Parameter 0, Second Question mark will be Parameter 1 so on and so forth.

Click OK to finish configuring the Incremental Load in the SSIS package. Let us Run the package

Let us open the SQL Server management studio and check whether we inserted new records and updated old records using Incremental load in ssis or not.

I suggest you to refer Better Approach – Incremental Load article to reference the Incremental Load further.