Here at Codit we try to be proactive and regularly check the health of BizTalk environments at our customers.
Part of that habit involves upgrading BizTalk environments regularly, a task which is handled by our Managed Services team.
Some of these updates involve installing CU (Cumulative Update) packages when they have been released for a while.
In one scenario one of those environments needed upgrading to BizTalk 2010 CU5.
During the same update procedure, CU2 of the BizTalk 2010 Adapter Pack (Adapter Pack 2010) was also released and as such we decided to take this CU into account as well.
After installing this on a test environment and after rebooting, things seemed to be working perfectly.
Only the day after we came to several of these errors in several of our flows:
A message sent to adapter "WCF-SQL" on send port "SP_SendPort_SQL" with URI "mssql://server//database?" is suspended. Error details: System.FormatException: Failed to convert parameter value from a String to a Int32. ---> System.FormatException: Input string was not in a correct format. at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType) --- End of inner exception stack trace --- Server stack trace: at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result) at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result) at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object outs, IAsyncResult result) at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result) Exception rethrown at : at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result) at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)
The error occurred in several flows and seems to be a problem in the send port.
Our send port uses a WCF-SQL adapter (sqlBinding) so this was our first indication of an issue with the CU2 of the adapter pack.
After some searching, I came accross this blog post which states the issue very clearly:
SITUATION BEFORE ADAPTER PACK 2010 CU2
If an empty element is specified for a stored procedure argument in the instance XML, the WCF-Custom adapter will specify DBNull as the parameter value.
However, this is not the case for table operations(such as table insert), an empty string will be specified as the parameter value unless the “nil” attribute is present in the instance XML.
If the “nil” attribute is set as true in the instance XML for table operation, then DBNull will be used as the parameter value, the FormatException won’t be thrown out.
That’s why we didn’t see any exception reported previously.
SITUATION AFTER ADAPTER PACK 2010 CU2
We made the code paths consistent between table operations and stored procedure invocation.
That is, when the element is empty, the DBNull will be used as parameter value if “nil” attribute is set as “true”, Otherwise an empty string will be used as the parameter value.
Please note that using DBNull always can cause problems if the parameter is used to populate a table column that is marked as not nullable. This is the right behavior after Adapter Pack 2010 CU2.
As such, this is a breaking change!
Off course, it makes a lot of sense to streamline behavior for the WCF-SQL adapter and to be honest, the behavior for stored procedure invocation was not optimal.
However, it might force any BizTalk developers that have relied on the current behavior of the adapter to face the consequences of this change.
Developers have relied on the “old” behavior and the same error (listed above) might occur when upgrading their environments to CU2 or even CU3 (whenever it comes out).
I hope this saves someone a bit of time in case they bounce into the problem.
Now I’m not sure if the same already applies to the BizTalk Adapter Pack 2013, but once I find the time to test this, I will surely update this blog post.
If you have any information that might help us and our readers on the 2013 adapter pack behavior, please let me know here in the comments.