I have a series of DTS packages that execute on a scheduled requency.

The various jobs import data from multiple data sources.

In case the scheduled job should not execute for any given day, the query
that gathers information ready for importing into SQL always gathers the last
three days worth of data.

There is a unique index on the table receiving data that is set to ignore
duplicates.

All though the message is a warning from SQL server, it still fails the DTS
job.

Is there anyway that I can get the DTS jobs to ignore the message if it is
duplicate key ignores and complete the step successfully?

If I were doing this, I would try to minimize duplicates at the source by
using DISTINCT in the source query. Or possibly transfer the data into a
pending table then weeding out unwanted items before the final INSERT.

Any other ideas?

Thomas Bradshaw
MyWebGrocer.com
Data Integration Services

I personally dislike doing it like this. I always think that a dupe
should be caught and we should not just spray data into a table knowing
there may be duplicates. I would use a query and or stage the data to
compare the keys coming in to the keys you already have and only insert
those you do not.

I know it is more work I just think it is also cleaner.

allan

"MavRx" <MavRx (AT) discussions (DOT) microsoft.com> wrote

What is the purpose of the ignore duplicates index then? I maintain a new
seeded single column key on the new table. The composite key from the old
table is maintained by the ignore duplicates index.

The purpose of this feature is to account for the job not running on any
given day. There is always an overlap of data collated from the source by
design. This means that if a job fails on day 1 & 2 but executes on day
three then data for the past three days is inserted.

This minimises the need for human administration.

My question is really, how do I check that on any given step how can I trap
the error message from SQL server and get DTS to ignore any given err

"Thomas Bradshaw" wrote: