Creating a SQL Server Identity Column with Talend Open Studio

Creating a SQL Server Identity Column with Talend Open Studio

Talend Open Studio for Data Integration will execute table-creating DDL as part of a data transformation.  When using Microsoft SQL Server as a target, use the "Specify identity field" to define the column with IDENTITY(1,1).

The tMSSqlOutput component in Talend Open Studio for Data Integration v5 will create tables if the "Create table" or similar action is used.  Build up the target schema using the Edit schema dialog, adding a column for the auto increment identity.

Basic Tab Configuration of tMSSqlOutput

In this example, nonResRealPropertyId is an Integer (DB Type INT) flagged as a key.  nonResRealPropertyId appears in the drop down next to the "Specify identify field".

Schema with an Identity Column

You'll also need to make sure that the transformation does not attempt to write to the auto increment column since the value will be provided with the database.  Even if you don't explicitly map a value to the auto increment column, Talend will attempt to insert a null into the column which will result in an error.

Don't Write to the Identity

This protection is given using the Field options on the Advanced tab.

Set Insert / Update Behavior for nonResRealPropertyId

Another possibility is to use a subset of the schema (minus the identity column).  I don't prefer this since it makes the metadata out-of-sync with the database schema.  This causes Built-ins schemas to proliferate which takes away global edits driven off the database.

For more flexibility, you might consider working with the Additional columns.  See the following blog post for an example using this technique.

Controlling the DDL Behind Talend Open Studio's tMySqlOutput