'Identity Insert off' not detected, validation error

adolf garlic

I am trying to capture the rows that fail during an insert.

The insert is an OLE DB command component.

I have config'd errors to redirect to a flat file.

My problem is this

I need to override the identity, fine if you are using 'fast load' but then you can't capture the redirected rows.

So I am using the regular 'table or view' data access mode, but then I lose the 'keep identity' checkbox.

At this point I add in another OLE DB command "SET IDENTITY_INSERT table OFF" before the actual insert ODB component.

But now it won't validate. I changed the task 'delay validation' property to true, but still it is failing.

It seemingly cannot detect this statement.

So how then can I capture error rows from an insert where I need to switch the identity off

[This is a one off historic load I am working on]

DarrenSQLIS


I think the SET IDENTITY_INSERT tble OFF option should work. Use an Exec SQL Task to run the command, and make sure you use the same connection for this task as you use in the OLE-DB Command/Destination, AND you must turn the connection's RetainSameConnection property to true.

adolf garlic

I have looked in the connection manager and cannot see the property

I have looked in the exec SQL task and cannot see the property

I have looked in Microsoft SQL Server 2005 Integration Services (K Haselden) which makes reference to this on page 183, and it says it is a property of the connection manager.

It ain't thar.

Is I a bit thick

DarrenSQLIS

It is a property on the connection as I said, and Kirk has written, so I don't know why can you not find. There are not many properties of an OLE-DB connection, and one of them is RetainSameConnection. It is only shown in the properties grid, not the pretty form.

adolf garlic

Thanks

I'm never using the dialogs again!

With FTP task, every time you open the dialog, it deletes the password. How stupid that not all the properties are shown

adolf garlic

still doesn't fix the original problem though

Error: 0xC0202049 at Data Cleansing, Deal Insert [12194]: Failure inserting into the read-only column "DealID".

Error: 0xC0202045 at Data Cleansing, Deal Insert [12194]: Column metadata validation failed.

Error: 0xC004706B at Data Cleansing, DTS.Pipeline: "component "Deal Insert" (12194)" failed validation and returned validation status "VS_ISBROKEN".

Error: 0xC004700C at Data Cleansing, DTS.Pipeline: One or more component failed validation.

Error: 0xC0024107 at Data Cleansing: There were errors during task validation.

adolf garlic

set

maximum insert commit size

to something other than zero

in the ole db destination

and the 'fast load' allows you to log error rows

DarrenSQLIS

What happens if one row is in error, but your commit size is 10, how many rows go own the error output