First published on MSDN on Aug 03, 2009
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
}
