Description:

This step assigns sequential numeric identifiers to a specified column in an ETL dataset.

The Set Number Identity Column ETL step generates an incrementing numeric value for each processed row, starting with a configurable starting value, and assigns it to the selected destination column. This enables ETL workflows to create sequential identifiers for imported or transformed records before loading them into destination systems.

The generated numeric identities can be used as record numbers, import identifiers, sequence numbers, surrogate keys, or application-specific identifiers where sequential numbering is preferred over GUID values.

This step provides an efficient way to automate numeric identity generation without relying on database-generated identity columns.

The step supports:

• Automatic sequential number generation
• Configurable starting value
• Identity assignment for every processed row
• Configurable destination column
• Integration with FlowWright ETL pipelines
• Automatic sequential record identification

This step can be used for:

• ETL data migration
• Database imports
• Sequential record numbering
• Surrogate key generation
• Data warehouse loading
• Legacy system integration
• Business process automation

Usage:

The Set Number Identity Column step is typically placed immediately before inserting records into a destination database or exporting transformed data.

The ETL pipeline generates a sequential numeric value for each processed row and stores it in the specified destination column before subsequent transformation or load operations are executed.

A typical ETL workflow might look like this:

Once the numeric identity values have been generated, later ETL steps can:

• Map destination fields
• Insert records into databases
• Export transformed datasets
• Generate surrogate keys
• Synchronize external systems
• Continue ETL processing

The destination column should exist within the ETL dataset before this step is executed.

Prerequisite:

The ETL process engine service should be running to execute the ETL definitions.

Example:

Let’s build and execute the “clsNumericIdentityDef” example.          

• Create a new ETL definition named “clsNumericIdentityDef” and open it in designer mode.
• Drag “clsInputCSV, clsNumericIdentity, clsTransformReplaceText, and clsOutputCSV” steps to the canvas. 
• Connect the dots between the “Start” and other steps, as shown above.
• Define a variable or a global required for the execution.
• Click the "clsInputCSV" step to configure its "Required" properties. Provide a name for the step. Specify the path to the input CSV file on the application server. Select the ETL data schema from the drop-down list. Note: The schema's column names should match those in the input file. Click the Save button. Click here to learn about the ETL Data Schema Designer.

• Click the "clsInputCSV" step to configure its "Optional" properties. Select the Show Schema button. The pop-up window displays the schema columns as shown below. This function helps to understand the schema at a glance.  

• Click the "clsNumericIdentity" step to configure its "Required" properties. Provide a name for the step, the column name to set, and the starting value for the identity column. Click the Save button. 

• The “Logging” configuration is necessary for documentation and also measures workflow progress and percent complete. This is achieved by configuring the step state and percent fields individually, as shown below. Configure the “Logging” using the following properties.

• Click the "clsTransformReplaceText" step to configure its "Required" properties. Provide a name for the step. Click the button to configure the columns to transform. Then click the Save button. 

• Click the "clsTransformReplaceText" step to configure its "Required" properties. Click the button to open the transform list configuration window. Click the Add Row (+) button to insert an empty row. Select the text column from the drop-down list. Enter the “find word” and “replace word” in the transformation columns. Select the checkbox to replace the field value. Click the Save button. You may add multiple columns for transformation by using the Add Row button.  

• Click the "clsOutputCSV" step to configure its "Required" properties. Enter a name for the step. Enter the path to the output CSV file on the application server. Click the Save button. 

• Click the "clsOutputCSV" step to configure its "Optional" properties. Provide a name for the step. Provide the variable or global reference to store the virtual output path. Click the Save button. 

• Save the ETL Definition. Execute the ETL process. When the ETL pipeline reaches the Set Number Identity Column step, FlowWright generates sequential numeric values beginning with the configured starting value and assigns each generated number to the specified destination column. After the numeric identities have been assigned, ETL processing automatically continues to the next transformation or destination step. Verify the processed dataset to confirm that each record contains a sequential numeric value in the configured destination column.

• The ETL transform pivot output file is included for reference.

Tips:

• Ensure that the specified column exists within the ETL dataset before executing the process.
• Select a starting value that does not conflict with existing records in the destination system.
• Use numeric identities when destination systems require sequential numbering rather than GUID values.
• Configure this step before inserting records into databases that require application-generated numeric keys.
• Use meaningful column names such as RecordID, SequenceNumber, ImportID, or CustomerNumber.
• Avoid overwriting existing identity values unless replacement is intentional.
• Combine this step with Column Mapping, Data Transformation, Database Insert, Lookup, or Export ETL steps to automate complete ETL and migration workflows.

Notes:

• A new numeric identity value is generated for every processed row.
• Identity generation begins with the configured Starting Value and increments sequentially for each subsequent record.
• The destination column should support numeric data types.
• Existing column values may be replaced if the configured column already contains data.
• The ETL process should validate destination schema compatibility before loading records.
• Numeric identity generation occurs entirely within the ETL pipeline and does not require database identity columns.
• This step is intended for ETL workflows and does not define True or False workflow return paths.

Definition Sample:

You may download the sample definition(s) from the link provided and later import them (drag-and-drop) into your FlowWright ETL Definition page.

Note: Verify and complete any missing configuration after importing the sample, including:

• Destination column name
• Starting value
• Source dataset configuration
• Column mappings
• Destination database schema
• ETL transformation settings
• Environment-specific settings

After verifying the configuration, save the ETL Definition before execution.

Click here to download the sample file.