In this article, we will show you how to perform Incremental Load in SSIS with examples. SSIS Incremental Load means comparing the target table against the source data based on Id, Date Stamp, or Time Stamp.
- If there are any New records in the Source data, we must 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, we must 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 the target table and update data present in 2, 5, and 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 onto 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: 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 the [CDC Target] table in the Database to insert the new records.
STEP 11: Click on the Mappings tab to check whether the source columns are 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 select the already created OLE DB Connection from the list.
Within the Component Properties, we must write the SQL Statement to perform updates. Please select the SqlCommand property and click on the browse button (… button). Once you click 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 must assign the appropriate column name to the parameters.
TIP: First Question mark will be Parameter 0, Second Question mark will be Parameter 1, 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 refer to the Better Approach – Incremental Load article to reference the Incremental Load further.
