sql server dts: hi all !!!

the most common reason for DDQ failure I suffer are duplicate key in
inserts.

I wonder what's the best way to handle this errors within the
transformations.

what I would do is:
for each record to insert, run a lookup query to check if exists
if doesn't exist, do an DTSTransformstat_InsertQuery
if exists, do an update DTSTransformstat_UpdateQuery

this seems to be slow.

does anybody know any better way to get the same result ?

thanks in advance,
ant.

This is probably not you are expecting as an answer, but I found DDQs to be
really really slow. You should be able to achive the same with better
performance using SELECT queries. You can find some examples here:

http://vyaskn.tripod.com/sql_server_dts_best_practices.htm

Search for DDQ on this page.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm




[quoted text, click to view]
hi all !!!

the most common reason for DDQ failure I suffer are duplicate key in
inserts.

I wonder what's the best way to handle this errors within the
transformations.

what I would do is:
for each record to insert, run a lookup query to check if exists
if doesn't exist, do an DTSTransformstat_InsertQuery
if exists, do an update DTSTransformstat_UpdateQuery

this seems to be slow.

does anybody know any better way to get the same result ?

thanks in advance,
ant.

Personally I only use the DDQ to load a data warehouse when I have Updates,
Inserts and Deletes because it gives me some flexibility.

You can do this a number of different ways

1. Pump the whole source table to the destination and then use TSQL to do
the INSERT, UPDATE, DELETE statements
2. Have the Source log changes to another table and pump that across. Your
table would have an idetifier attribute so you can tell if it was Updated,
Inserted or Deleted.



--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


[quoted text, click to view]