Hi,

Attempting to load data from a flat file into a SQL Server 2005 db. using
the Data Flow Task.
However, some of the data contains duplicate records, throwing up an error
on the PK constraint.
I hoped that SSIS would be able to handle this using the new inbuilt Error
Handling.
However, when I set this on the Data Source Transform, and redirect failing
rows to an error table, the package still fails and no rows are re-directed.

Am I correct in thinking that SSIS only automatically handles certain types
of transform errors such as datatype conversions and expressions errors, but
cannot handle the PK constraint error ?
If so do I need to follow the same process I would have done in DTS and
build a staging table without the constraints and cleanse the data there
first ?
Are there any better methods than a staging table to cleanse the data ?

Thanks.
Paul W.

Not an answer, but you should try posting to here
http://groups-beta.google.com/group/...ntegrationsvcs

It's the Integration Services version of this DTS group. You might
have more luck.




On Oct 24, 11:11 am, PRW <P... (AT) discussions (DOT) microsoft.com> wrote:

Thanks for reply.

I did manage to find out the answer myself eventually.

Just to clarify a point though. I thought that this forum was for both dts
and ssis. Is that not correct ?
ssis does not appear under the Enterprise Development newsgroup, but is
often referred to as dts (still in MS code too !).

"weelin" wrote:

Could you post your solution? I am having the same or similar problem
and would like to know how you resolved it.

Thank you,
Bryant

PRW wrote:

Use the Sort transformation.
There is a check box to remove duplicate rows.

I also used another Sort transformation to not remove duplicate rows and
then compared the data between the two Sort outputs to show which actual rows
which were causing the duplicate data.

Hope that helps. I do also believe there are two other transformation
methods of doing this by can't clarify as I didn't test them. I recall one
was the Aggregate transform.

"akaitatsu (AT) gmail (DOT) com" wrote:

Thanks! That was very helpful.

PRW wrote: