In this article we will show you, What will be the better approach to configure SSIS Incremental Load with example. We already explained Incremental Load in our previous example but we used OLE Db Command Transformation to update the records in target table. Although this approach is good for small amount of data but, for large data sets there will be performance issue. In this example we will show you the steps involved in configuring the SSIS Incremental Load. 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.
Configuring SSIS Incremental Load
The OLE Db Command Transformation perform updates on Row by row bases, which is time-consuming. In this example we will create temporary table or staging table to store the updated information and then pass those records to target table using Execute SQL task.
STEP 1: Drag and drop the Data Flow Task and two Execute SQL tasks from toolbox to control flow region and rename the First Execute Task as Create Stating table, Data Flow Task as SSIS Incremental Load and last task as Update the destination table.
- Create Staging table: This may be global temporary table or any permanent table to store update information. For every package run, existing table will be dropped and recreated. Remember, you can also use Truncate statement to truncate the table, rather than dropping and recreating.
- Data Flow Task: Loading data from Source to Destination
- Updating Destination Table: Update the target table data with the data present in Staging table
STEP 2: Double click on the Execute SQL Task will open task editor. Here, we are creating staging table on our target database so, we selected the already created OLE DB Connection Manager pointing to SSIS Tutorials database. Please click on the browse button beside the SQL Statement property to write the custom SQL statement
SQL Command we used in the above screenshot is:
-- DROPPING EXISTING TABLE
IF OBJECT_ID('CDC_Staging', 'U') IS NOT NULL
DROP TABLE CDC_Staging;
-- CREATING NEW TABLE
CREATE TABLE CDC_Staging
[Employee_ID] [int] PRIMARY KEY NOT NULL,
[FirstName] [nvarchar](255) NULL,
[LastName] [nvarchar](255) NULL,
[Education] [nvarchar](255) NULL,
[Occupation] [nvarchar](255) NULL,
[YearlyIncome] [float] NULL,
[Sales] [float] NULL
Click OK to finish creating staging table
Next, Double click on the Data Flow Task to open the data flow tab.
STEP 3: Drag and drop OLE DB Source from toolbox to data flow region. Double click on OLE DB source in the data flow region will open the OLE DB connection manager settings. From the below screenshot you can observe that, We selected [SSIS Tutorials] Database as source database and [CDC Source] as source table
STEP 4: Click on columns tab to verify the columns. You can uncheck the unwanted columns here (if any).
Click OK to close the OLE DB source and Drag the Lookup Transformation from toolbox to the Data Flow tab.
STEP 5: 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 6: Click on the connection Tab to configure the lookup connections settings. If you have already created the OLE DB connection Manager then select from the list otherwise, Click New button and configure it. Here we selected the existing one which is pointing to 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.
STEP 7: Click on the Available Input columns and drag the required column onto 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 8: 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 9: 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 below screenshot you can observe that, We used 1 condition and one default output in Conditional Split Transformation So, We get 2 output in total.
STEP 10: Drag and drop two OLE DB Destinations 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 11: Double click on the first 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 12: 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 13: Double click on the second OLE DB Destination will open the OLE DB Destination Editor. Select the OLE DB Connection manager and select [CDC_Staging] table present in the [SSIS Tutorials] Database.
TIP: If you don’t find the table then run the first Execute SQL Task alone to create.
STEP 14: Click on Mappings tab to check whether the source columns are exactly mapped to the destination columns.
Before we step into the control flow tab, Please change the Validate External Metadata from TRUE to False because we are creating staging table on fly (Run time)
STEP 15: Within the Control flow, Double click on the second Execute SQL Task to open task editor. Here, we are updating the [CDC Target] data with staging table data so, we selected the already created OLE DB Connection Manager pointing to SSIS Tutorials database. Please click on the browse button beside the SQL Statement property to write the custom SQL statement
To update the records, SQL Command we use in the above screenshot is:
UPDATE [dbo].[CDC Target]
SET [FirstName] = Staging.[FirstName]
,[LastName] = Staging.[LastName]
,[Education] = Staging.[Education]
,[Occupation] = Staging.[Occupation]
,[YearlyIncome] = Staging.[YearlyIncome]
,[Sales] = Staging.[Sales]
FROM [CDC Target]
[CDC_Staging] AS Staging
ON [CDC Target].Employee_ID = Staging.Employee_ID
Click OK to finish configuring the SSIS Incremental Load 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 SSIS Incremental load along with staging tables or not.
Thank You for Visiting Our Blog