The method I describe in my post about handling COMExceptions during package generation works if you have control over the package generation code, but sometimes you’ll be using third party libraries, or debugging after the fact.
Note, the error codes, symbolic names, and descriptions for all of the SSIS HResults can all be found on the Integration Services Error and Message Reference page in Books Online. This should be your first stop if you want to quickly (and manually) lookup an SSIS error code.
However, if you want to do this programmatically…
The symbolic name for each SSIS error is slightly more useful than the hex error code value, and will sometimes be enough for you to isolate your problem right away. You can programmatically determine the symbolic name by comparing the error code value against the members of the HResults class.
public static string GetSymbolicName(int errorCode)
{
string symbolicName = string.Empty;
HResults hresults = new HResults();
foreach (FieldInfo fieldInfo in hresults.GetType().GetFields())
{
if ((int)fieldInfo.GetValue(hresults) == errorCode)
{
symbolicName = fieldInfo.Name;
break;
}
}
return symbolicName;
}
I could use this function in a try/catch block to give the user additional information as to why the error occurred.
catch (COMException ex)
{
string symbolicName = GetSymbolicName(ex.ErrorCode);
Console.WriteLine("Symbolic Name: {0}", symbolicName);
}
Retrieving the actual error message for a given SSIS HResult is a little more involved. We can use the FormatMessage API to pull the message directly out of the dtsmsg100.dll (in 2005, it’s dtsmsg.dll). To do this, we’ll need to expose a couple of native methods so we can pinvoke them.
static class NativeMethods
{
[DllImport("kernel32.dll")]
public static extern IntPtr LoadLibrary(string dllToLoad);
[DllImport("kernel32.dll")]
public static extern bool FreeLibrary(IntPtr hModule);
[DllImport("Kernel32.dll", SetLastError = true)]
public static extern uint FormatMessage(uint dwFlags, IntPtr lpSource,
uint dwMessageId, uint dwLanguageId, ref IntPtr lpBuffer,
uint nSize, IntPtr pArguments);
public const int FORMAT_MESSAGE_ALLOCATE_BUFFER = 256;
public const int FORMAT_MESSAGE_IGNORE_INSERTS = 512;
public const int FORMAT_MESSAGE_FROM_STRING = 1024;
public const int FORMAT_MESSAGE_FROM_HMODULE = 2048;
public const int FORMAT_MESSAGE_FROM_SYSTEM = 4096;
public const int FORMAT_MESSAGE_ARGUMENT_ARRAY = 8192;
public const int FORMAT_MESSAGE_MAX_WIDTH_MASK = 255;
}
First we’ll get a handle to dtsmsg100.dll using LoadLibrary:
IntPtr handle = NativeMethods.LoadLibrary("dtsmsg100.dll");
if (handle == IntPtr.Zero)
{
throw new Exception("Couldn't open library dtsmsg100.dll");
}
Then we’ll call FormatMessage. The first parameter is a set of flags – we’ll want the native method to do the allocation, the messages to be loaded from a specific DLL (dtsmsg), and we don’t want to do any parameter substitution.
const int FormatMessageFlags = NativeMethods.FORMAT_MESSAGE_ALLOCATE_BUFFER | // FormatMessage will allocate buffer
NativeMethods.FORMAT_MESSAGE_FROM_HMODULE | // Messages are loaded from specified DLL
NativeMethods.FORMAT_MESSAGE_IGNORE_INSERTS; // Don't perform place holder substitutions
IntPtr lpMsgBuf = IntPtr.Zero;
uint dwChars = NativeMethods.FormatMessage(
FormatMessageFlags,
handle, // handle to error message dll
errorCode, // error code we're looking up
0, // 0 for default language
ref lpMsgBuf, // message buffer
2048, // max size of message buffer
IntPtr.Zero); // substitution arguments
if (dwChars == 0)
{
// FormatMessage will set LastError
throw new Win32Exception();
}
string sMessage = Marshal.PtrToStringAnsi(lpMsgBuf);
// Free the buffer
Marshal.FreeHGlobal(lpMsgBuf);
Once we have the error message, we can release the handle to dtsmsg using FreeLibrary.
Using the same scenario from my previous post, getting a COMException off a call to AcquireConnection() gives me an HResult of 0xC020801C. Programmatically retrieving the error message gives me this:
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "%1" failed with error code 0x%2!8.8X!. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Notice the format placeholders (%1 and %2!8.8X!) weren’t filled in. This is because we used the FORMAT_MESSAGE_IGNORE_INSERTS flag on FormatMessage – we wouldn’t have known what values to put in there, since we weren’t the ones that raised the original exception.
I’ve encapsulated all of this logic in the HResultsHelper class. I’ve included the full code listing (including a CreatePackage method from previous API posts) here.
-------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using System.Runtime.InteropServices;
using System.ComponentModel;
using System.Globalization;
using System.Reflection;
namespace ErrorMsgFromHResult
{
public class HResultHelper : IDisposable
{
const string MessageDLL = @"dtsmsg100.dll";
private IntPtr handle = IntPtr.Zero;
bool disposed;
const int FormatMessageFlags = NativeMethods.FORMAT_MESSAGE_ALLOCATE_BUFFER | // FormatMessage will allocate buffer
NativeMethods.FORMAT_MESSAGE_FROM_HMODULE | // Messages are loaded from specified DLL
NativeMethods.FORMAT_MESSAGE_IGNORE_INSERTS; // Don't perform place holder substitutions
public HResultHelper()
{
// Load the DLL we'll get the error messages from
handle = NativeMethods.LoadLibrary(MessageDLL);
if (handle == IntPtr.Zero)
{
throw new Exception(string.Format(CultureInfo.CurrentCulture, "Couldn't open library: {0}", MessageDLL));
}
}
public string GetErrorMessage(COMException comException)
{
return GetErrorMessage((uint)comException.ErrorCode);
}
public string GetErrorMessage(uint errorCode)
{
IntPtr lpMsgBuf = IntPtr.Zero;
uint dwChars = NativeMethods.FormatMessage(
FormatMessageFlags,
handle, // handle to error message dll
errorCode, // error code we're looking up
0, // 0 for default language
ref lpMsgBuf, // message buffer
2048, // max size of message buffer
IntPtr.Zero); // substitution arguments
if (dwChars == 0)
{
// FormatMessage will set LastError
throw new Win32Exception();
}
string sRet = Marshal.PtrToStringAnsi(lpMsgBuf);
// Free the buffer
Marshal.FreeHGlobal(lpMsgBuf);
return sRet;
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
private void Dispose(bool disposing)
{
if(!this.disposed)
{
NativeMethods.FreeLibrary(handle);
handle = IntPtr.Zero;
disposed = true;
}
}
~HResultHelper()
{
Dispose(false);
}
static class NativeMethods
{
[DllImport("kernel32.dll")]
public static extern IntPtr LoadLibrary(string dllToLoad);
[DllImport("kernel32.dll")]
public static extern bool FreeLibrary(IntPtr hModule);
[DllImport("Kernel32.dll", SetLastError = true)]
public static extern uint FormatMessage(uint dwFlags, IntPtr lpSource,
uint dwMessageId, uint dwLanguageId, ref IntPtr lpBuffer,
uint nSize, IntPtr pArguments);
public const int FORMAT_MESSAGE_ALLOCATE_BUFFER = 256;
public const int FORMAT_MESSAGE_IGNORE_INSERTS = 512;
public const int FORMAT_MESSAGE_FROM_STRING = 1024;
public const int FORMAT_MESSAGE_FROM_HMODULE = 2048;
public const int FORMAT_MESSAGE_FROM_SYSTEM = 4096;
public const int FORMAT_MESSAGE_ARGUMENT_ARRAY = 8192;
public const int FORMAT_MESSAGE_MAX_WIDTH_MASK = 255;
}
}
class Program
{
static void Main(string[] args)
{
HResultHelper helper = new HResultHelper();
try
{
CreatePackage();
}
catch (COMException ex)
{
string errorMessage = helper.GetErrorMessage(ex);
Console.WriteLine(errorMessage);
}
helper.Dispose();
}
static Package CreatePackage()
{
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);
//
// Add connection manager
//
ConnectionManager connection = package.Connections.Add("OLEDB");
connection.Name = "localhost";
connection.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorksDW2008;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Connect Timeout=1";
//
// Add OLEDB Source
//
IDTSComponentMetaData100 srcComponent = pipeline.ComponentMetaDataCollection.New();
srcComponent.ComponentClassID = "DTSAdapter.OleDbSource";
srcComponent.ValidateExternalMetadata = true;
IDTSDesigntimeComponent100 srcDesignTimeComponent = srcComponent.Instantiate();
srcDesignTimeComponent.ProvideComponentProperties();
srcComponent.Name = "OleDb Source";
// Configure it to read from the given table
srcDesignTimeComponent.SetComponentProperty("AccessMode", 0);
srcDesignTimeComponent.SetComponentProperty("OpenRowset", "[DimCustomer]");
// Set the connection manager
srcComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(connection);
srcComponent.RuntimeConnectionCollection[0].ConnectionManagerID = connection.ID;
// Retrieve the column metadata
srcDesignTimeComponent.AcquireConnections(null);
srcDesignTimeComponent.ReinitializeMetaData();
srcDesignTimeComponent.ReleaseConnections();
//
// Add OLEDB Destination
//
IDTSComponentMetaData100 destComponent = pipeline.ComponentMetaDataCollection.New();
destComponent.ComponentClassID = "DTSAdapter.OleDbDestination";
destComponent.ValidateExternalMetadata = true;
IDTSDesigntimeComponent100 destDesignTimeComponent = destComponent.Instantiate();
destDesignTimeComponent.ProvideComponentProperties();
destComponent.Name = "OleDb Destination";
destDesignTimeComponent.SetComponentProperty("AccessMode", 3);
destDesignTimeComponent.SetComponentProperty("OpenRowset", "[DimCustomer_Copy]");
// set connection
destComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(connection);
destComponent.RuntimeConnectionCollection[0].ConnectionManagerID = connection.ID;
// get metadata
destDesignTimeComponent.AcquireConnections(null);
destDesignTimeComponent.ReinitializeMetaData();
destDesignTimeComponent.ReleaseConnections();
//
// Connect source and destination
//
IDTSPath100 path = pipeline.PathCollection.New();
path.AttachPathAndPropagateNotifications(srcComponent.OutputCollection[0], destComponent.InputCollection[0]);
//
// Configure the destination
//
IDTSInput100 destInput = destComponent.InputCollection[0];
IDTSVirtualInput100 destVirInput = destInput.GetVirtualInput();
IDTSInputColumnCollection100 destInputCols = destInput.InputColumnCollection;
IDTSExternalMetadataColumnCollection100 destExtCols = destInput.ExternalMetadataColumnCollection;
IDTSOutputColumnCollection100 sourceColumns = srcComponent.OutputCollection[0].OutputColumnCollection;
// The OLEDB destination requires you to hook up the external columns
foreach (IDTSOutputColumn100 outputCol in sourceColumns)
{
// Get the external column id
IDTSExternalMetadataColumn100 extCol = (IDTSExternalMetadataColumn100)destExtCols[outputCol.Name];
if (extCol != null)
{
// Create an input column from an output col of previous component.
destVirInput.SetUsageType(outputCol.ID, DTSUsageType.UT_READONLY);
IDTSInputColumn100 inputCol = destInputCols.GetInputColumnByLineageID(outputCol.ID);
if (inputCol != null)
{
// map the input column with an external metadata column
destDesignTimeComponent.MapInputColumn(destInput.ID, inputCol.ID, extCol.ID);
}
}
}
return package;
}
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
}
}
}
