Issue with DTS Package.

Hi,
There are 2 DTS packages that uses different source tables from sql servers and Oracle databases to populate few tables in one destination
sql server. At the end of each datapumptask , upon successful completion in all DTS packages,
A record is inserted in a table in the destination sql server.

Though all data transfer is successfull, all 4 dts packages returns with exit code 1 step failed.

On looking in the details further I noticed that the packages are executed as follows

package 1: Starts at 6:00 am takes 1hr 30 mins
package 2: starts at 7:00 am takes 10 seconds
package 3: starts at 8:00 am takes 4 seconds
Package 4: starts at 9:00 am takes 8 minutes

As these packages use the same table to insert a record upon successfull completion, My guess is due to overlap in execution
it could have caused a deadlock and hence returning exit code 1.

1) If the reason is deadlock then without changing the schedule by changing the transaction isolation level to avoid the deadlock
can we make this work. (At the moment it is default read committed).

2) If the reason could be something else please let me know your thoughts.


Regards,
Shivi

Thanks Satya,

I already did that and the problem is solved.The culprit was the last insert process.
The table to which arecord is inserted has a trigger which then updates various other tables.

In that one of the subquery was returning multiple values during an update.

Anyway this has been sorted and without the package log I would not have gone to this level.

Regards,
Shivi