SQL Server Performance Forum – Threads Archive
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
Enable the DTS package loggin for all the packages in order to see what is the problem.
Take help of books online to enable the logging on DTS packages. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
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
Glad you’ve sorted the problem, but it will be a good thing to have such package log to check at first instance. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
]]>