SQL Server Performance

Issue with DTS Package.

Discussion in 'SQL Server DTS-Related Questions' started by shivi, Oct 27, 2006.

  1. shivi New Member


    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
  2. satya Moderator

    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.
  3. shivi New Member

    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
  4. satya Moderator

    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.

Share This Page