Post reply

Re-Starting DTS Package

  • July 11, 2005 at 8:56 pm

    #66727

    I have a number of DTS packages which import data from Oracle to SQL Server.  Each package imports a single table using the following steps:  Drop PK, truncate table, import from Oracle to SQL Server, (on success) recreate PK.  From time to time one of the packages fails during the import phase.  I need to re-start the package on failure of the import phase.  I have tried to use the on failure workflow to point back to the truncate table portion of the Package, but this doesn't seem to work.  Suggestions, please. 

  • July 12, 2005 at 7:21 am

    #573333

    You could create a control DTS package which would call each import package and then check the results. If it (the child) doesn't complete successfully you can then re-run it and or send emails.

    Check this link out for a good example.

    http://www.sqldts.com/default.aspx?215

    Good Luck,

    Darrell

  • July 13, 2005 at 8:51 am

    #573948

    You could change the "retry attempts" in the job that runs the DTS to something greater than 0.

  • July 13, 2005 at 3:07 pm

    #574156

    The retry attempts is probably the best solution to my problem.  I learn something new every day that I read this forum.  Thanks to both of you.

  • July 13, 2005 at 3:15 pm

    #574162

    Yeah, this site is very helpful.  I would also have to echo DSP's suggestion to check out http://www.SQLDTS.com.  You will learn much from them as well.

  • July 13, 2005 at 5:47 pm

    #574207

    You would need to put an ActiveXScript task on the "On failure" workflow that sets the Execution status of the truncate table step to Waiting.

    There is an article on http://www.sqldts.com that illustrates the looping technique.

     

    --------------------
    Colt 45 - the original point and click interface

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply