Task Factory - Mark Marinovic
"We bought Task Factory primarily for the SCD Type 2 task, as it enabled us to do several things the built-in SSIS SCD task did not do. Task Factory comes highly recommended from me as our ROI was reached in just days."

Mark Marinovic

Task Factory
"I have found Task Factory to be a huge time saver with SSIS development. The SalesForce components alone saved close to two months of work on a major integration project. I highly recommend!"
Alan Rubel
Database Administrator
Joint Commission Resources
Task Factory - Jay Cole
"I purchase Task Factory over a year ago, and let me tell you, IT WORKS FLAWLESSLY. I haven’t had a single problem with it, and I used to HATE having to make changes with the version you’re using. My advice (for what it’s worth) is to pay the money!"

Jay Cole

Task Factory - Urban Outfitters
"We have found that Task Factory tremendously speeds up the development process and improves performance by means of the high-performance components."

Tim Harris

BI Data Engineer

Urban Outfitters Inc.

Task Factory Loews Corporation
"We bought the product mostly because of the company's reputation for the service they provide and the specific need of a secure SFTP task."

Matt Cushing

Application Systems Analyst/Developer

Loews Corporation

Greater New York City Area



Buy Now Free Trial Download Compare Editions More Features

Task Factory Upsert Destination (Batch Update Or Insert)

Helps synchronize two systems no matter the source or destination. Many companies struggle with the development of loading procedures that determine whether the row is new or to be updated. The Upsert Destination reads the rows coming into the data flow and detects if the row should be inserted or updated. This saves the developer a long development cycle and could save up to 90% on each execution of a package.

Feature Highlights

  • Conditionally inserts or updates data into a table
  • Inserts are performed using bulk insert which is significantly faster compared to row-by-row approach
  • Four methods in which data can be updated

 

Upsert Destination (Batch Update Or Insert)

Upsert Destination - Step 1
Double click the Upsert destination to configure it. First, create an ADO.net connection manager.

Double click the Upsert destination to configure it. First, create an ADO.net connection manager.

Upsert Destination - Step 2
Next, select the table access mode. There are two options for this- Table, or TableFromVariable.

Next, select the table access mode. There are two options for this- Table, or TableFromVariable.

Upsert Destination - Step 3
In the Destination Table Name menu, select either the table that the data will be upserted into, or the variable that will provide the table name.

In the Destination Table Name menu, select either the table that the data will be upserted into, or the variable that will provide the table name.

Upsert Destination - Step 4
In the Destination Table Name menu, select either the table that the data will be upserted into, or the Choose whether Upsert will insert or update records in the destination table. When the 'Update' action is checked, the 'Update' tab will show up at the top of the UI. The 'Enable Identity Insert' is automatically selected when Upsert detects if the destination table contains an identity column. If the selected source for Upsert doesn't contain a column that will insert data into the identity column, uncheck this selection. If the source will contain at least some rows that will be inserted into the identity column, leave this selected checked.that will provide the table name.

In the Destination Table Name menu, select either the table that the data will be upserted into, or the Choose whether Upsert will insert or update records in the destination table. When the 'Update' action is checked, the 'Update' tab will show up at the top of the UI. The 'Enable Identity Insert' is automatically selected when Upsert detects if the destination table contains an identity column. If the selected source for Upsert doesn't contain a column that will insert data into the identity column, uncheck this selection. If the source will contain at least some rows that will be inserted into the identity column, leave this selected checked.that will provide the table name.

Upsert Destination - Step 5
The column mappings section allows you to map the columns from the source to the columns in the destination. If the same column name exists in the source and destination then the mappings will automatically be mapped. You must select at least on column to be used as the key. Select the column is the primary key in the destination table. If the destination table doesn't have a primary key (which is bad), select the column that contains unique values for each row.

The column mappings section allows you to map the columns from the source to the columns in the destination. If the same column name exists in the source and destination then the mappings will automatically be mapped. You must select at least on column to be used as the key. Select the column is the primary key in the destination table. If the destination table doesn't have a primary key (which is bad), select the column that contains unique values for each row.

Upsert Destination - Step 6
The update method tab allow you to control if updates occur on the selected destination table by giving you four options.

The update method tab allow you to control if updates occur on the selected destination table by giving you four options.

Upsert Destination - Step 7
Bulk Update - Bulk update is the default update method. In this method the destination is updated with whatever data is contained in the source as long as the keys selected in the columns mappings from the general tab match in the source and destination. It does not take into account whether data has changed or not. 

Column Compare - Column compare can be used to compare whether data in the source doesn't match what's in the destination. If the data doesn't match in the selected columns, the update occurs. Otherwise the update will not be performed. Keep in mind that this method will slow down the upsert process because of the comparison process. Select the columns (only columns mapped in the general tab are available) that you would like to be compared in the grid by checking the checkbox next to the source column name.

Timestamp Compare – Timestamp compare will update the destination based on a timestamp value that exists in both the source and destination.

Last Update Compare - Last update compare will compare a date column from the source to a date column in the destination. If the dates do not match, the row is updated.

Update 'Last Modified' Column - The Update 'Last Modified' Column allows you to select a column to be updated with the date and time of when the row was updated.

Bulk Update - Bulk update is the default update method. In this method the destination is updated with whatever data is contained in the source as long as the keys selected in the columns mappings from the general tab match in the source and destination. It does not take into account whether data has changed or not. 

Column Compare - Column compare can be used to compare whether data in the source doesn't match what's in the destination. If the data doesn't match in the selected columns, the update occurs. Otherwise the update will not be performed. Keep in mind that this method will slow down the upsert process because of the comparison process. Select the columns (only columns mapped in the general tab are available) that you would like to be compared in the grid by checking the checkbox next to the source column name.

Timestamp Compare – Timestamp compare will update the destination based on a timestamp value that exists in both the source and destination.

Last Update Compare - Last update compare will compare a date column from the source to a date column in the destination. If the dates do not match, the row is updated.

Update 'Last Modified' Column - The Update 'Last Modified' Column allows you to select a column to be updated with the date and time of when the row was updated.

Upsert Destination - Step 8


View Our Suite of Pragmatic Works Products
Copyright 2014 by Pragmatic Works