We have a DTS package which we use to pull changed
transactions from Oracle into a SQL Server data warehouse.
The package steps pull the data from Oracle into a
temporary table, then inserts the NEW records into the
target, then updates the CHANGED records into the target.
The CHANGED records portion of the task is where the
deadlock occurs. Here the package uses a data driven query
task where the SOURCE does:

select a.* from temp a inner join target b
where a.key = b.key and a.updated <> b.updated

The BINDINGS window points back to the same target table
used for the join in the SOURCE. A majority of the time,
the package executes fine, but on occasion, the package
deadlocks with itself, however, the batch job that runs
the package simply continues to "run".

MS support did not have a good answer "separate the
functionality so that the DTS task is not attempting to
select and update the same table". My position is that if
it ever worked it should always work, or there should be a
parm that allows me to specify serialization of the update
after the select.

Does anyone have a suggestion? Any help would be much
appreciated.

Greg

OKthis is how I implement the DDQ

On the destination data source I create a table the exact same structure as
the source and use that as the BINDINGS table.

I then create and map my statements.

Let me know if that helps.

On the workflow properties for the DDQ set "Close Connection on Completion"



--

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


"Greg Simpson" <Greg.Simpson (AT) MyFlorida (DOT) com> wrote