Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11

    Unanswered: Scanning many SSIS packages for a particular property

    Hello, all. Sorry if this is a bit of a newbish question, but I am a bit stumped.

    So I have a pile of around 50 packages (*.dtsx files) or so, and we have found that not all of these packages are set to have the critical bits fail the package on failure of the critical bits. This gets to be slightly annoying when there is a job of some 20 or 30 packages running, and one of these fails without a single apology, and the rest of the job runs along as if nothing (bad) happened. This leads to having to hunt down what failed, diagnosing why it failed, and then re-running the parts that failed in different and inventive ways after the unhandled exception.

    So far, I have broken open a couple of the offending SSIS packages in notepad, and the string "FailPackageOnFailure" appears in a number of places. Unfortunately, due to the fact that some of the packages have Sequence Containers that hold some of the SQLTasks (all of the "critical bits" are SQL Tasks, I believe), and this changes the XML path to the parts that I want to scan. This leaves me with the prospect of opening each package, wait for the package to be validated, going to the SQL Task in the package, right clicking for the properties of said SQL Task, and finding the Fail Package on Failure bit (OK, more likely I will delegate that task to some poor schmuck, but I promise I will feel some regret over it).

    So, the questions are

    1) Has anyone else had to do this?
    b) Did they find a better way to do this?
    iii) Are there utilities to make this any easier?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I figured I would post this solution, in case anyone else has this problem. It will take me a little while to sort out the various XML queries, but the description is very promising:
    Quote Originally Posted by Jamie Thompson
    Note that this one will also return all tasks that exist in eventhandlers and it ignores how “deep” a task is in the container hierarchy.

    I'm off to crash the server. Wish me luck!

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by MCrowley View Post
    I'm off to crash the server. Wish me luck!
    Happy crashing!

    Please keep us posted on the crashes, and the end-results!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Well, I finally uncovered the problem. I was wrong about the FailPackageOnFailure setting. It seems that is set to a default of 0 (I assume false). It seems the faulty packages had MaxErrorCount set to 5 at the package level. This caused the package throwing only one error report a success status, since it still had 3 more free errors (at 5 the package would fail).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •