September 5, 2014 at 6:05 am
Trying to keep this as simple as possible.
SQL2008R2
I have a source that uses change tracking - we need to pull through and handle deletes on the target.
1.) The target has been set-up as a view on a table with not null-able columns.
2.) The view has a trigger which uses a merge to either insert, update or delete.
When deleted record inserts into the view via SSIS, SSIS fails with the message ""The value violated the integrity constraints for the column." - the relevant columns are null, because they were deleted. But this is handled in the MERGE - it does not try and insert.
If I insert the same deleted record manually into the view, with null columns - it deletes the record.
If I take the NOT NULL constraint off the target, the record deletes without error.
I have tried turning CHECK CONSTRAINTS off in SSIS, no difference.
Anyone have advice?
Obviously, there are alternatives to the way this is currently done -but the change on our side would be pretty significant.
September 5, 2014 at 6:19 am
xrapidx (9/5/2014)
Trying to keep this as simple as possible.SQL2008R2
I have a source that uses change tracking - we need to pull through and handle deletes on the target.
1.) The target has been set-up as a view on a table with not null-able columns.
2.) The view has a trigger which uses a merge to either insert, update or delete.
When deleted record inserts into the view via SSIS, SSIS fails with the message ""The value violated the integrity constraints for the column." - the relevant columns are null, because they were deleted. But this is handled in the MERGE - it does not try and insert.
If I insert the same deleted record manually into the view, with null columns - it deletes the record.
If I take the NOT NULL constraint off the target, the record deletes without error.
I have tried turning CHECK CONSTRAINTS off in SSIS, no difference.
Anyone have advice?
Obviously, there are alternatives to the way this is currently done -but the change on our side would be pretty significant.
when you say manually, can you share any example that how your are able to do it. some DDL and sample data if possible it will help to understand the issue.
September 5, 2014 at 6:30 am
If I do this, where ColumnA and ColumnB is NOT NULL
insert into [extract].[vw_MERGE_SourceTable]
(RowID, ColumnA, ColumnB,InsertDate, UpdateDate,RowAction)
values
(100, NULL, NULL, NULL, GETDATE(), GETDATE(),'D')
The trigger on vw_MERGE_SourceTable deletes the record from SourceTable.
The trigger is as follows
/* SYSTEM GENERATED using */
CREATE TRIGGER [extract].[tg_MERGE_SourceTable]
ON [extract].[vw_MERGE_SourceTable] INSTEAD OF INSERT
AS
SET NOCOUNT ON;
MERGE[extract].SourceTable
USINGINSERTED
ON SourceTable.RowID = INSERTED.RowID
WHEN MATCHED AND INSERTED.[RowAction] = 'D' THEN DELETE
WHEN MATCHED AND INSERTED.[RowAction] <> 'D' AND
((SourceTable.ColumnA <> INSERTED.ColumnA OR
(SourceTable.ColumnB <> INSERTED.ColumnB))
THEN UPDATE
SETColumnA = INSERTED.ColumnA,
ColumnB = INSERTED.ColumnB,
RowAction = INSERTED.RowAction,
UpdateDate = GETDATE()
WHEN NOT MATCHED AND INSERTED.RowAction <> 'D'
THEN INSERT
(RowID,
ColumnA,
ColumnB,
InsertDate,
UpdateDate,
RowAction)
VALUES
(INSERTED.RowID,
INSERTED.ColumnA,
INSERTED.ColumnB,
INSERTED.InsertDate,
INSERTED.UpdateDate,
INSERTED.RowAction);
If exactly the same data comes in via SSIS dataflow into vw_MERGE_SourceTable - it fails with the violation. (FIRE_TRIGGERS is set on the view)
September 5, 2014 at 7:59 am
ok, so now can you share how you are doing it on SSIS, as its works on teh avaliable meta data for a given object. i-e, if the SSIS see its having not null able then it will give you error. its because trigger its working on RDBMS, but SSIS does not.
try the following:
1) create a staging table and insert all the deletable data.
2) Execute SQL task and use the same logic to insert that that directly to this view.
September 5, 2014 at 8:21 am
SSIS is a straight connection to the view using OLEDB destination fast load, , check constraints off, fire triggers option.
I checkes the meta data on the ssis side, it doesn't seem to store whether the column is null able
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply