Four Stars

Big-Data tMSSQLBulkExec Bulk Update returns Identity column 'PK_column' contains invalid INCREMENT

I am working on an ETL Process that inserts new data and updates changed data from a staging table into a dimension table in a SQL Server Database.

I have set up the table in SQL Server so that it looks like this:

CREATE TABLE [schema].[dim_learning_activity](
    [dim_learning_activity_id] [bigint] IDENTITY(1,1) NOT NULL,
    [activity_fk] [bigint] NOT NULL,
    [name] [nvarchar](500) NOT NULL,
    [code] [nvarchar](500) NULL,
    [description] [nvarchar](500) NULL,
    [label] [nvarchar](500) NULL,
    [date_last_update] [date] NULL,
    [start_date] [date] NULL,
    [end_date] [date] NULL,
    [active] [int] NULL,
    [est_cred_hours] [float] NULL,
    [ConType_Name] [nvarchar](500) NULL,
    [Txt1] [nvarchar](500) NULL,
    [TechnicalCourse] [nvarchar](500) NULL,
    [TechnicalCourseCategory] [nvarchar](500) NULL,
    [CreationDt] [date] NULL, CONSTRAINT [PK_dim_learning_activity_id] PRIMARY KEY CLUSTERED (
    [dim_learning_activity_id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_dim_learning_activity] UNIQUE NONCLUSTERED (
    [activity_fk] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]GO

Where you can see the dim_learning_activity_id is the Primary Key.

Below is the setup I currently have in Talend:talend-etl-bulk-update-key-error.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The file that Talend outputs is in the proper format (columns delimited by "%%" and rows by "\n"):

5458%%108129%%Learning Item Name%%108129%%%%Curriculum%%2018-05-11%%%%%%1%%0.5%%Products (Technical Content)%%Technical%%Technical%%Delta%%2018-05-11

Which equates to:

dim_learning_activity_id = 5458
activity_fk = 108129
name = Learning Item Name
code = 108129
description = null
label = Curriculum
date_last_update = 2018-05-11
start_date = null
end_date = null
active = 1
est_cred_hours = 0.5 ConType_Name = Products (Technical Content) Txt1 = Technical TechnicalCourse = Technical TechnicalCourseCategory = Delta CreationDt = 2018-05-11

The record is already in the dimension table, but it will not update with the Talend tMSSQLBulkExec with the Bulk Update option selected.

I have tried completely wiping the table and starting from scratch with it and still receive this error, even on a fresh run of the job, when the table is blank and all it is doing is inserting, the Update errors out with the same error.

I have also tried updating on the activity_fk (System of Record ID), which results in the same error.

Please let me know if there is more information you need to help.