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 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

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 - 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 - 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.



Buy Now Free Trial Download Compare Editions More Features

Task Factory Surrogate Key Transform

Adds an identifier column to a data flow used to uniquely identify a row.

Feature Highlights

  • Adds an identifier column to a data flow used to uniquely identify a row. This is useful if your data source does not contain an identifier or you simply want to add a row counter to your data flow.
  • Allows the use of a variable to determine seed and increment
Surrogate Key Transform

Surrogate Key Transform - Step 1
Using the Surrogate Key UI

The Surrogate Key Transform will add a unique identifer column to source data. Through the UI you can add the output column name, set the seed and increment of the output column generated by the surrogate key transform.

Using the Surrogate Key UI

The Surrogate Key Transform will add a unique identifer column to source data. Through the UI you can add the output column name, set the seed and increment of the output column generated by the surrogate key transform.

Surrogate Key Transform - Step 2

Output Column Name

The output column name is used to define the name of the surrogate key column that will be included in the transform when executed. 
For instance, if you named your output column name SK_COLUMN, that column will be included in the output of the transform.

Output Column Name

The output column name is used to define the name of the surrogate key column that will be included in the transform when executed. 
For instance, if you named your output column name SK_COLUMN, that column will be included in the output of the transform.

Surrogate Key Transform - Step 3
Counter Options

Reset counter after each run - This option will simply add a surrogate key value to the data flow that starts at the seed value and increments based on the increment value.

Save counter to database after each run - This option gives you the option of saving the surrogate key value to a table in your database after each run. Choosing this option enables a few more options you must configure under the "Database Options" section. Each time the surrogate key transform is run, the key value is retrieve from the table in the "Table for counter storage".

Counter Options

Reset counter after each run - This option will simply add a surrogate key value to the data flow that starts at the seed value and increments based on the increment value.

Save counter to database after each run - This option gives you the option of saving the surrogate key value to a table in your database after each run. Choosing this option enables a few more options you must configure under the "Database Options" section. Each time the surrogate key transform is run, the key value is retrieve from the table in the "Table for counter storage".

Surrogate Key Transform - Step 4

Connection Manager - Select the connection manager where contains (or you will create) the table which will store the surrogate key values.

Table for counter storage - If you have already created the table in another surrogate key transform, enter that table name here. If you haven't created a table, use this to enter the name of the new table and click "Create Table".

Create Table Button
- Clicking the create table button will attempt to create a table in the database selected in the Connection Manager drop down. You will receive a message that gives you the SQL that will be executed against your database:


Connection Manager - Select the connection manager where contains (or you will create) the table which will store the surrogate key values.

Table for counter storage - If you have already created the table in another surrogate key transform, enter that table name here. If you haven't created a table, use this to enter the name of the new table and click "Create Table".

Create Table Button
- Clicking the create table button will attempt to create a table in the database selected in the Connection Manager drop down. You will receive a message that gives you the SQL that will be executed against your database:


Surrogate Key Transform - Step 5

Important information about how the surrogate key is stored

When the surrogate key is stored in a table, each instance of the surrogate key in any ssis package is given a row identifier in the table which corresponds to the RowIdentifier property of a Surrogate Key Transform. That means EACH surrogate key transform used will create it's own row and not share the value of other surrogate key transforms.

Important information about how the surrogate key is stored

When the surrogate key is stored in a table, each instance of the surrogate key in any ssis package is given a row identifier in the table which corresponds to the RowIdentifier property of a Surrogate Key Transform. That means EACH surrogate key transform used will create it's own row and not share the value of other surrogate key transforms.

Surrogate Key Transform - Step 6

Seed

The seed property is used to define the starting point for the surrogate key data. You can define the seed two ways.

Seed

The seed property is used to define the starting point for the surrogate key data. You can define the seed two ways.

Surrogate Key Transform - Step 7

Increment

The increment property is used to define the increment (the number the key is increased for each row) of the surrogate key data. You can define the increment two ways.

Increment

The increment property is used to define the increment (the number the key is increased for each row) of the surrogate key data. You can define the increment two ways.

Surrogate Key Transform - Step 8
Max Value

Max value is used to put a maximum number on the surrogate key value. The default is the max value of an DT_I4 column or 2147483647. If the value enter is greater than 2147483647, the output column that contains the surrogate key value (e.g. SK_COLUMN) is set to a DT_I8 value.

Recycle Max Value
- This option will reset the value of the surrogate key value to the Seed value when the max value is reached if checked. If left unchecked, the transform will fail and will tell you the max value has been reached.

Max Value

Max value is used to put a maximum number on the surrogate key value. The default is the max value of an DT_I4 column or 2147483647. If the value enter is greater than 2147483647, the output column that contains the surrogate key value (e.g. SK_COLUMN) is set to a DT_I8 value.

Recycle Max Value
- This option will reset the value of the surrogate key value to the Seed value when the max value is reached if checked. If left unchecked, the transform will fail and will tell you the max value has been reached.

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