Debugging a COMException during package generation

Debugging a COMException during package generation

Rate This
  • Comments 3

You’ll occasionally receive a COMException when you’re programmatically generating SSIS package – typically when dealing with Data Flow components. These COM Exceptions will provide you with an HRESULT (ErrorCode), and no additional information.

System.Runtime.InteropServices.COMException (0xC020801C): Exception from HRESULT: 0xC020801C
   at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.AcquireConnections(Object pTransaction)
   at MyApp.Program.CreatePackage()
   at MyApp.Program.Main(String[] args)

Ideally the exception you receive would have the underlying error message included. Unfortunately, the SSIS pipeline API doesn’t provide the IErrorInfo support needed for the .NET runtime to determine the error details, but there is a way for you to receive the error(s) using an event handler.

For this to work, you’ll need to be able to modify the package generation code (or at least control the TaskHost and MainPipe of the data flow objects). You’ll receive detailed error messages by supplying an IDTSComponentEvents handler for the IDTSPipeline100.Events property. The FireError event will be raised right before any COMException is thrown, proving you the error details.

The first step is to create a class which implements the IDTSComponentEvents interface. This class is simply going to report all errors and warnings out to the console as they occur.

class ComponentEventHandler : IDTSComponentEvents
{
    public bool FireError(int errorCode, string subComponent, string description, string helpFile, int helpContext)
    {
        Console.WriteLine("[Error] {0}: {2}", subComponent, description);
        return true;
    }
[…]
}

Next, we’ll hook up an instance of this class to the Events property of our MainPipe (Data Flow) object.

Package package = new Package();

// Add Data Flow Task
Executable dataFlowTask = package.Executables.Add("STOCK:PipelineTask");

// Set the name (otherwise it will be a random GUID value)
TaskHost taskHost = dataFlowTask as TaskHost;
taskHost.Name = "Data Flow Task";

// We need a reference to the InnerObject to add items to the data flow
MainPipe pipeline = taskHost.InnerObject as MainPipe;

// Set the IDTSComponentEvent handler to capture the details from any 
// COMExceptions raised during package generation
ComponentEventHandler events = new ComponentEventHandler();
pipeline.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);

 

After this, any COMExceptions raised by the pipeline will be displayed on the console.

For example, let’s look at the COM Exception I listed at the top of this post (HRESULT 0xC020801C). Looking up the error code on the Integration Services Error and Message Reference page tells me that the HRESULT maps to DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER, so there’s a good chance one of my calls to AcquireConnection() is causing the exception. If my package contained a number of data sources and connection managers, it could make it very difficult to debug.

After hooking up the event handler, I see the following on the command line before receiving the exception:

[Error] OleDb Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "localhost" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Now I know which source is failing (OleDb Source [1]), and which connection manager (localhost) is causing the problem. It could still be better, but now at least I know where to start debugging (ie. make sure that the database on localhost is up and running).

Here is a full implementation of IDTSComponentEvents incase you want to try it out yourself.

--------

class ComponentEventHandler : IDTSComponentEvents
{
    private void HandleEvent(string type, string subComponent, string description)
    {
        Console.WriteLine("[{0}] {1}: {2}", type, subComponent, description);
    }

    #region IDTSComponentEvents Members

    public void FireBreakpointHit(BreakpointTarget breakpointTarget)
    {
    }

    public void FireCustomEvent(string eventName, string eventText, ref object[] arguments, string subComponent, ref bool fireAgain)
    {
    }

    public bool FireError(int errorCode, string subComponent, string description, string helpFile, int helpContext)
    {
        HandleEvent("Error", subComponent, description);
        return true;
    }

    public void FireInformation(int informationCode, string subComponent, string description, string helpFile, int helpContext, ref bool fireAgain)
    {
        HandleEvent("Information", subComponent, description);
    }

    public void FireProgress(string progressDescription, int percentComplete, int progressCountLow, int progressCountHigh, string subComponent, ref bool fireAgain)
    {
    }

    public bool FireQueryCancel()
    {
        return true;
    }

    public void FireWarning(int warningCode, string subComponent, string description, string helpFile, int helpContext)
    {
        HandleEvent("Warning", subComponent, description);    
    }

    #endregion
}
Leave a Comment
  • Please add 6 and 6 and type the answer here:
  • Post
  • We are currently developing a BI  "Sausage factory" as we call it where we define sources, and have extract, archive and staging layers be generated programmatically, both on the DB side and the SSIS side.

    I saw this post a couple of months ago, but thought it to much of a bother (I am no C# guy), to implement it. I just created the class and added it to our toolbox, and all ready I have saved perhaps an hours work debugging.

    Thanks for this ;)

  • This was simply the best code snippet :)... Saved hours of debugging time

  • This is a life saver. This code is not easily available. Thank you.

Page 1 of 1 (3 items)