My previous post was marked/flagged as spam. This is not a spam message. Please do not flag this topic. I am in need of assistance with this issue.
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.
What if you try to insert the same record manually from outside your job?
Yes, an MSSQL query UPDATE table_name SET column = 'value' WHERE dim_learning_activity_id = @var works just fine, and even an MSSQLOutput works with update on key, but not the Bulk Update.