SQL identity column insert using pentaho data integration

Question

I am new to Pentaho data integration tool.I am trying to move data from a source table into target table ... both is SQL Server. The tables are identical and has an identity column.

Tried many options but ... it gives an error every time saying "Indentity insert is set to OFF"

Tried introducing a hop inbetween to execute a SQL statement to "SET identity_insert tblname ON" .. still dint work.

Any suggestions would be highly appreciated.

Thanks.

Answer 1

Putting that in a hop certainly wont work, because PDI/kettle uses a connection(s) per step. You need to put that setting in the advanced options of the database connection and then you should be ok - it will then be used for all instances of that database connection.

Also make sure you "share" your database connections, otherwise if you create them from hand in every transformation you'll need to apply that setting to every single database connection in each transformation. ( Unless you're using a database or EE repository in which case the connections are centralised so you're ok )

Answer 2

One other thing you can try is to remove the identity columns from the select you are using to pass from the source to the destination.

This way, you will make sure that SQL will create a new identity for each one of the rows intead of trying to insert them,

Answer 3

You should add a command after db connection established.