SQL Server Performance


Discussion in 'SQL Server DTS-Related Questions' started by lara99, Mar 1, 2008.

  1. lara99 New Member

    Hi Friends, I am trying to automated a major job, The sql code is in 4000 line I have broke this into small parts and kept it in execute sql task in to seperate package.
    After that I have made a parent DTS package which have all the child packages in it. Now I have to run the parent package using dtsrunui .
    Problem is if any of the packge is failed dtsrunui doesnt stop there it keeps on executing the packages.
    Is there a way that we can use to make dts packages to go down if any of the child packages fails.
    Thank You,
  2. martins New Member

    Yes, for some reason SQL 2000 child packages don't pass errors to the parent package properly...so you will have to check the logs and explicitly raise an error.
    Create an execute sql task with the following code after each child package execution, and replace with the names where needed:
    DECLARE @Error_Code AS INT
    SET @Error_Code = 0
    SELECT @Error_Code = ISNULL(MAX(s.errorcode), 0)
    FROM msdb.dbo.sysdtspackagelog p
    ,msdb.dbo.sysdtssteplog s
    WHERE p.name = '<child package name>'
    AND p.lineagefull = s.lineagefull
    AND p.starttime = (SELECT MAX(p1.starttime)
    FROM msdb.dbo.sysdtspackagelog p1
    WHERE p.name = p1.name
    AND s.errorcode <> 0
    IF @Error_Code <> 0
    RAISERROR ('Child package error: <child package name>', 16, 1)
  3. lara99 New Member

    Thank You , for a valuable reply, but I had some more question, In this context if I add this task would this going to stop the package execution, or package would keep executing.
    And I tried doing it with child jobs with one consolidated master job.
    what I did is I have schedule every dts package and when the job is created I kept that job into master job. when I executed master job though I have selected the workflow properties as on failure action job must go down, but jobs never go down, is there a way out to rectify it.
    I hope I am able to explain you my situation clearly.
    Thank You,
  4. martins New Member

    Yes, it would stop the execution of the package provided that you have an on success workflow condition after the script and before executing the next dts package, and if you set the "fail package on first error" property.
    I have never tried to do this with jobs, but I assume that you would have to do something similar.

Share This Page