2

PDI 9.0

Simple job MS SQL connection Table input -> tableouput

tableouput table : CREATE TABLE [dbo].[a00]( [ProductID] [int] NOT NULL [IDENTITY(1,1)][1] ..

I need to write IDENTITY explicitly in SSMS I run set IDENTITY_INSERT a00 ON and doing insert ok.

In pentaho I got error Table output.0 - Cannot insert explicit value for identity column in table 'a00' when IDENTITY_INSERT is set to OFF.

trying to run in SQl : set IDENTITY_INSERT a00 ON Before
SQL -> table input -> table output but same error

Seems set IDENTITY_INSERT a00 ON runs in DIFFerent SESSION then table output

How can I run set IDENTITY_INSERT a00 ON in same session or point out to use it ;

FedorFFF
  • 21
  • 2

3 Answers3

0

Pentaho tries to do everything in parallel while on the same Transformation. So you need to create a job and do it sequentialy 1st - SQL script "SET IDENTITY_INSERT a00 ON" 2nd - Your transformation with the table output 3th - SQL script "SET IDENTITY_INSERT a00 OFF"

Example:

JOB

Hope this helps.

rmorales
  • 61
  • 4
  • Hi Idea was good but do Not work out set IDENTITY_INSERT a00 ON ; Same error Seems it runs in OTHER session Question does this work at your PC ? – FedorFFF Mar 05 '20 at 17:21
0

I checked using profiler SQL run in one session transformation in another here is problem do not know how to resolev it

FedorFFF
  • 21
  • 2
0

I solved this problem by adding an extra option to my database connection. Under "Advanced" in the database connection, enter a SQL statement that will be executed right after connecting to the database.

My statement (MSSQL) looks like

set identity_insert [dbname].[schemaname].[tablename] on;

Your tablename could also be set with a variable.

Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
Patricio
  • 1
  • 1