Here is a ZIP file <click here> of a nice SQL component ready to use in any BizTalk project that needs SQL interaction. Normally you would use the SQL adapter for that but the SQL adapter is not that great to work with. This is caused by several factors.
- The transaction level is always serializable
- The message still has to travel through the messagebox, then to the adapter, etc etc. this can take quite some time.
- Adding point 1 and 2 together makes it really slow.
- As a result of point 3 (beeing really slow) you end up with deadlocks and suspended messages in the HAT
Sometimes you don’t want all this overhead but then sometimes you do want to talk to SQL and execute a stored procedure. That is when the BizTalk.Sql.Helper component can be of service. It is a simple but quite versatile component. Here are some advantages of this component.
- The component can be configured and stored in SSO. See John Flanders Excellent article about storing config items in SSO here <click here> for the 2006 variant or <click here> for the BizTalk 2004 variant of SSO config.
- No roundtrip in the messagebox neccesary
- If a SQL command fails there is no suspended message
Below is a screenshot of how you can configure this component with SSO config :
and for sure you can instantiate the component from within an orchestration as well..
You can save the command to an XML document and instantiate the component from an XML document, same with the parameters.
Some other methods of the component are sqlHelper.DataToXml() to get an XML representation of the dataset, use sqlHelper.DataToXsd() to get the XSD that represents the data.
I hope somebody will use the component and I am always curious about feedback….