Wednesday, August 15, 2012

Ask the BI Admin: How do I get meaningful error information from a Script Task in SSIS?

Most of us have seen the dreaded SSIS Script Task error and struggled to understand what it is trying to tell us. If you haven't seen it before, take a look at this:
Error: 0x6 at Really Bad Script Task: The script returned a failure result.
Mind-numbingly useless. There is a way, however, to get what you need if you put a little forethought into building your Script Tasks.


I am cheating a little bit on this post and borrowing the solution from the ASp.net c# sql blog. The blog hasn't been updated in quite a while and the post I am sourcing (SSIS: Integration services: Throw error from Script Task (Error Handling)) is over four years old at this point. On the off-chance it goes away, I will still have the necessary information right here.

So, I created a Script Task in SSIS with the code shown below. This is my "repro" step and will demonstrate the problem.

public void Main()
{
  try
  {
    int zero = 0;
    int impossible = 1 / zero;
    Dts.TaskResult = (int)ScriptResults.Success;
  }
  catch
  {
    Dts.TaskResult = (int)ScriptResults.Failure;
  }
}
Sure enough, we get the infamous error when we try and run the package. The lines of code that set the TaskResult property of the Dts object give us a little clue as to how to solve the problem. If we can report back success or failure of the Script Task, is it possible we can report back more? Yes, you can.


The Dts object has an Events property which, in turn, has a FireError method. That's where we can tell the SSIS engine what's going on so it can report back to us. The FireError method accepts, requires actually, the following parameters:
  • errorCode (int/Integer): An arbitrary error code you can provide. I can't find any documentation that suggests that this really means anything to anyone other than the developer of the package.
  • subComponent (string): The official documentation (linked below) says that this is to provide more detail about the event source. I suggest using the package name as the value for this. You probably don't need to specify the name of the script task since it is included in the error message after the "Error: 0x6" bit.
  • description (string): This is the big important one. This is where you want to provide the details about the exception that occurred. You just need to catch the exception and pass all its contents to this parameter.
  • helpFile (string) and helpContext (int/Integer): These two are mostly useless unless you're going to publish help files for your SSIS packages. Yeah, I didn't think so.
I was going to tell you that you could find more information in the IDTSComponentEvents.FireError Method article but there really isn't much more to it, at least nothing more that Microsoft wants to share with us.

So, with all that info, you can change the above code to look like this:
public void Main()
{
  try
  {
    int zero = 0;
    int impossible = 1 / zero;
    Dts.TaskResult = (int)ScriptResults.Success;
  }
  catch (Exception ex)
  {
    Dts.Events.FireError(-1, "MyPackageName", ex.ToString(), "", 0);
    Dts.TaskResult = (int)ScriptResults.Failure;
  }
}
We still get the useless 0x6 error but right before it in the Output window we get something useful:
Error: 0xFFFFFFFF at Not So Bad Script Task, MyPackageName: System.DivideByZeroException: Attempted to divide by zero.
   at ST_6624e360ee2f47b9b8e3dfe5ccae92ea.ScriptMain.Main()
Error: 0x6 at Not So Bad Script Task: The script returned a failure result.
There you go. A simple enhancement to your error handling and you get a great big assist with your troubleshooting efforts down the road.

Got something to add to this article or maybe you disagree with something? Please share your ideas with everyone by submitting a comment.

No comments:

Post a Comment