How can I execute a SQL script before each INSERT using Pentaho Spoon?

I am trying to use Spoon to migrate a database from a PostgreSQL database into a Sybase installation.

The problem I am facing right now is that Sybase has this mode you have to enable whenever you want to set an id explicitly. You have to execute the command SET IDENTITY_INSERT table_name ON, that will enable inserting data specifying ids for the current connection.

Is it possible to setup a transformation to execute that before a "Table output" step, using the same connection that will be used to insert the data?

Or am I approaching this the wrong way?

Solution

I've run into this and it's annoying. What I ended up doing is making a custom connection definition for each table that has an identity to insert.

In the custom connection definition, there is an advanced tab. On the advanced tab there is a space to enter custom SQL that get executed immediately after connecting. Put your SET statement in there, then select that connection in your table output step.

This is a common enough problem in both Sybase and MS SQL Server I should probably post it as a feature request to Pentaho.

Brian