Surrogate Key Transform
The "Surrogate Key Transform" will add a unique identifier column to source data. You can add the output column name, set the seed and increment of the output column generate by the surrogate key 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.
• Counter Options
o 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.
o 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 will enable a few more options you must configure under the "Database Options" section.
o Seed - The seed property is used to define the starting point for the surrogate key data. You can define the seed two ways.
■ Text - The surrogate key data will start with the number entered into the textbox.
■ Variable - The surrogate key data will start with the value from the selected variable.
o Increment - The increment property is used to define the increment of the surrogate key data. You can define the increment two ways.
■ Text - The surrogate key data will increment with the number entered into the textbox.
■ Variable - The surrogate key data will increment with the value from the variable selected.
o Max Value - Sets the maximum number the surrogate key can have. If you set it higher than the default, the output column that contains the surrogate key value is set to a DT_I8 value.
o Recycle Max Value - This option will rest the value of the surrogate key value to the Seed value when the max value is reached if checked. If unchecked, the transform will fail with an error stating the max value has been reached
• Database Options
o Connection Manager - Select the connection manager that contains the table which will store the surrogate key values.
o Table for counter storage - If the table is already created, enter that table name here. If you are creating a new one, enter the desired name for the table here and click create table.
o "Create Table" button - clicking this button will attempt to make a table using the specified name. Clicking the button will pop up a message showing the SQL statement that will be used to create the table.