SQL identity column insert using pentaho data integration

3
votes

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.

3
etlpentahodata-integration
You have to be careful. The SET command is only good for the session on which it was executed not any others. – bluevector
Thats the only option i could think of now ..and you response makes sense. Does the tool have any options to specify that at all ?? – UnlimitedMeals
I don't know Pentaho at all. I'm just writing from a SQL Server perspective. Can you write the SET statement in the tool's code or whatever? – bluevector

3 Answers

1
votes

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 )

0
votes

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,

0
votes

You should add a command after db connection established.

Image