Featured Post

Applying Email Validation to a JavaFX TextField Using Binding

This example uses the same controller as in a previous post but adds a use case to support email validation.  A Commons Validator object is ...

Sunday, December 18, 2011

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

3 comments:

  1. Thank you for this, I have been pulling my hair out over creating an identity field in Talend for hours now.

    ReplyDelete
  2. Thanks for your help, I have the same problem than anonymous, good job!!

    ReplyDelete
  3. how to get Return codes using database driven (table) with tdie or twarn components

    ReplyDelete