IDENTITY_INSERT is set to OFF exception

Symptoms

You may get the following error when inserting values into SQL Server with a tMSSQLRow or tMSSqlOutput component:

Exception in component tMSSqlOutput_1
java.sql.BatchUpdateException: IDENTITY_INSERT is set to OFF.
Cannot update identity column 'ID'
Cannot insert explicit value for identity column in table 'PERSON' when IDENTITY_INSERT is set to OFF.

This is because it is not allowed to insert an explicit value for an IDENTITY column when IDENTITY_INSERT is set to OFF. If there is an IDENTITY column in the table, for example, a table with an IDENTITY column defined as below:

CREATE TABLE person (id int IDENTITY PRIMARY KEY, name varchar(40))

and the IDENTITY_INSERT parameter is set to OFF in SQL Server, the above error will be thrown when inserting an explicit value for identity column.

Resolution

To solve this issue, you can follow one of the two solutions below according to your needs.

Don't insert an explicit value for an identity column

By default, SQL Server automatically inserts an increment value for an IDENTITY column, when the IDENTITY_INSERT parameter is set to OFF. If you don't need an explicit value for the IDENTITY column, remove the IDENTITY column from the component schema.

Turn on Identity insert

If you want to insert an explicit value for an identity column, select the Turn on Identity insert check box in the component Basic settings.