sql server dts: Foreign key constraint violation on import -- sql server dts

Foreign key constraint violation on import

bostonguy NO[at]SPAM gmail.com
9/1/2006 2:44:07 PM

sql server dts: I posted this on the Integration Services board, but figured the answer
to my problem might not depend on whether I am using SSIS or DTS.

Scenario:
I have a SSIS package that loads a flat file to a SQL Server database
via an OLE DB Destination object. The flat file contains a column,
which has a FK constraint on it. If the FK does not exist in the
primary table, SQL Server "throws" a FK constraint error and the entire

transaction is rolled back. I am executing the SSIS package
programmatically from a C# web form and as such, I am reporting the
error back to end user via the web page.

Questions:
How might I report back which specific record caused the error?
Does SQL Server provide this information? If not, any ideas on who to
pre-process the file to detect errors before trying to insert it?

These flat files are large so its important to report the exact problem

that caused the constraint violation.

TIA for your help.

Re: Foreign key constraint violation on import

Arnie Rowland
9/1/2006 2:51:23 PM

For a more 'robust' import process, I suggest that you first import the data
to a 'staging' table. Then any necessary data clean-up and modifications can
be done. The data can be checked for constraint consistency, and conforming
data can be moved to the production tables, leaving the non-conforming data
to be moved to an 'Exceptions' table for further inspection.

SSIS will be quite able to manage such a process for you.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

[quoted text, click to view]