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:
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.