DTS Package fails to import conforming rows of data

Question

DTS Package fails to import conforming rows of data

mngong

SSCertifiable

Points: 6167
More actions
September 20, 2005 at 1:30 pm

#363694

I have a DTS package which imports into a table To avoid duplicates there is a primary key .Once there is a single duplicate value the entire package fails rather than kicking out only the nonconforming value.
What can I do to get the rows that do not voilate the PK into my table
Thanks
Mike

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 1

you could insert into a process table and de-dupe before final import into production table. Could write process whereby you save the offending records to examine later.

mngong SSCertifiable Points: 6167 More actions · Answer 2

Thank you for your response

If anyone has done something in those lines and wants to share that will be appreciated

Thanks

Mike

Ray M SSC-Insane Points: 21093 More actions · Answer 3

Create a staging table as noted by andrewkane17,

Look at creating a lookup.

http://www.sqldts.com/default.aspx?277

or you need to create some error handling.

The process of "Skipping" a row because it violates a rule is not inherent in a dts package.

or perhaps this article will help

http://www.sqldts.com/default.aspx?266

sqldts has some good info.

Japie Botma SSCrazy Points: 2990 More actions · Answer 4

I ALWAYS use a staging table (I actually have a staging database) and remember to allow for NULLS on every field. You do not want your import to fail. It is easy to eliminate the duplicates and add any other criteria in your update procedure.

5ilverFox
Consulting DBA / Developer
South Africa

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 5

If the duplicate rows can be ignored, you could replace your primary key with a unique index with the IGNORE_DUP_KEY attribute (and same clustering as existing primary key).

If you need to do something with the duplicate rows, i.e. UPDATE instead of INSERT, stick with the staging table.

mngong SSCertifiable Points: 6167 More actions · Answer 6

Thanks to all who responded Veteran,s answer worked out well for me.

I am currently looking into changing the maximum number of allowed errors

in DTS

Mike

philcart SSC-Forever Points: 47794 More actions · Answer 7

Using a staging table is probably the better method and is more in line with standard practice.

You could also set the Exception file properties on the Options tab of the Datapump. Then you'll get the error rows dumped out to a file.

--------------------
Colt 45 - the original point and click interface