In this article we will show you, How to perform Incremental Load in SSIS with example. 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 target table. For example, every day we have to insert branch wise sales.
- If there are any updated values in Source data then we have to update those records in target table. For example, updating the customer address.
In this example we will show you the steps involved in configuring the Incremental Load in SSIS. Before we start creating the SSIS 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 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 toolbox to control flow region and rename it as Incremental Load in SSIS.
Double click on it and it will open the data flow tab.
From the below screenshot you can observe that, We selected [SSIS Tutorials] Database as source database and [CDC Source] as source table
STEP 3: Click on columns tab to verify the columns. In this tab we can uncheck the unwanted columns also.
Click OK to close the OLE DB source and Drag the Lookup Transformation from 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.
STEP 5: Click on the connection Tab to configure the lookup connections settings. If you already created the OLE DB connection Manager then select from the list otherwise, Click New button and configure it. Here we selected SSIS Tutorials Database.
Next, we are using the SQL Query as the lookup table because we require only one column to lookup (i.e., Employee_ID) so we are using the SQL query as shown below.
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 and drop the Conditional Split Transformation from toolbox to data flow region and connect the Lookup Match Output as the source to Conditional split
STEP 8: Double-click on it to provide the conditions. 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 there is 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 exists in the destination table. So we want to update the existing data with new data.
From the above you can observe that, We used 1 condition and one default output in Conditional Split Transformation So, We get 2 output 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 pop up window Input Output Selection Window will appear prompting you to select the Input Output type: either INSERT or UPDATE.
STEP 10: Double click on OLE DB Destination will open the OLE DB Destination Editor. Select the OLE DB Connection manager and select [CDC Target] table present in the [SSIS Tutorials] Database to insert the new records.
STEP 11: Click on 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 Employee_ID column because it is 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 as shown below. Please refer the OLEDB Connection Manager in SSIS article to understand, How to configure the connection manager.
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 be opened to write the SQL Statement. In this example, we are Updating the values in the products table.
SQL 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 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 further reference the Incremental Load.
Thank You for Visiting Our Blog