Recently there was a bug raised against BIDS Helper which originated in my Expression Editor control. Thankfully the person that raised it kindly included a screenshot, so I had the error code (HRESULT 0xC0017011) and a stack trace that pointed the finger firmly at my control, but no error message text. The code itself looked fine so I searched on the error code but got no results. I’d expected to get a hit from Books Online with the Integration Services Error and Message Reference topic at the very least, but no joy.
There is however a more accurate and definitive reference, namely the header file that defines all these codes dtsmsg.h which you can find at-
C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Include\dtsmsg.h
Looking the code up in the header file gave me a much more useful error message.
////////////////////////////////////////////////////////////////////////////
// The parameter is sensitive//
// MessageId: DTS_E_SENSITIVEPARAMVALUENOTALLOWED
//
// MessageText:
//
// Accessing value of the parameter variable for the sensitive parameter “%1!s!” is not allowed. Verify that the variable is used properly and that it protects the sensitive information.
//
#define DTS_E_SENSITIVEPARAMVALUENOTALLOWED ((HRESULT)0xC0017011L)
Unfortunately I’d forgotten all about this. By the time I had remembered about it, the person who raised the issue had managed to narrow it down to something to do with having sensitive parameter. Putting that together with the error message I’d finally found, a quick poke around in the code and I found the new GetSensitiveValue method which seemed to do the trick.
The HResult fields are also listed online but it only shows the short error message, and it doesn’t include that all so important HRESULT value itself. So let this be a lesson to you (and me!), if you need to check SSIS error go straight to the horses mouth – dtsmsg.h. This is particularly true when working with early builds, or CTP releases when we expect the documentation to be a bit behind.
There is also a programmatic approach to getting better SSIS error messages. I should to take another look at the error handling in the control, or the way it is hosted in BIDS Helper. I suspect that if I use an implementation of Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSInfoEvents100 I could catch the error itself and get the full error message text which I could then report back. This would obviously be a better user experience and also make it easier to diagnose any issues like this in the future. See ExprssionEvaluator.cs for an example of this in use in the Expression Editor control.