Migrating to WCF SQL Adapter from SQL Adapter (Receiving)
Migrating to WCF SQL Adapter from SQL Adapter (Receiving)
When migrating from the old deprecated SQL Adapter in BizTalk Server to the new WCF SQL Adapter, there are a few things to take into consideration. This article will try to explain how to do a one-to-one migration most painless and also discuss if such a migration is the best option.
We will focus on the receiving side of the Adapters in this blog. Another blog that covers the send side will follow.
Receiving using the old Adapter
When using the now deprecated SQL Adapter in BizTalk we need to have SQL Server return XML to the Adapter.
For this demo I have created two tables; a Blog table and a BlogComment table:
To do this we need to use the “FOR XML” clause when selecing the data.
The following is an example of a Stored Procedure that pools one new blog (if any) together with any comments associated with the blog and marks the blog as “read by BizTalk”.
create procedure dbo.GetNextBlog
declare @blogID int;
–Get one ID that has not yet been processed (if any)
select top(1) @blogID = blog.ID
where (blog.Processed is null);
–select the Blog together with Comments (if any) and return as XML
blog.ID, blog.BloggerID, blog.BlogText,blog.EntryDate,
comment.BloggerID as ‘CommentatorID’, comment.Comment, comment.EntryDate as ‘CommentDate’
on (blog.ID = comment.BlogID)
where (blog.ID = @blogID)
for xml auto, elements;
–Update the Blog entry and set Processed to now, so that it will not be fetched more than once
set Processed = GetDate()
where ID = @blogID;
Notice that I have renamed some column names that would otherwise cause ambiguous column names. This is because some of the polling mechanism in the new WCF SQL Adapter do not support multiple columns with the same name (Polling and TypedPolling).
Use the old SQL Adapter to call this Stored Procedure, and configure the Adapter like below:
Notice that the XML outputted from BizTalk is encoded as UTF-16 and not the normal default UTF-8. This is because I am using both PassThruReceive and PassThruSend Pipelines to pass the message directly through BizTalk, and the old SQL Adapter (for reasons unknown) encode the XML it sends through the Receive Port as UTF-16.
Receiving using the new WCF SQL Adapter
Let us try to receive the same blog-data, by using the new WCF-SQL Adapter instead.
There are three different options when receiving using this Adapter.
- Polling (Get non XML SQL data back in a loosely typed .NET data set structure)
- TypedPolling (Get non XML SQL data back in a strong typed XML structure)
Let’s look at the default behavior (Polling) first:
To use this behavior, we must remove the “FOR XML” clause in the Stored Procedure, since this type of Polling expects pure SQL data returned and not XML.
As shown this XML is pretty much useless in BizTalk, since the Message Type (namespace and root element) is weakly typed and would be the same for all types of documents. The reason the type is default, has to do with the fact that this is actually not a BizTalk Adapter but rather an WCF binding, that can also be used outside BizTalk in various .NET set up, where datasets might be used.
Let us try to use the TypedPolling feature instead.
This feature requires that we choose a unique InboundID for each Receive Location, which will be appended to the namespace of the XML we receive, in order for the document type to be unique in BizTalk.
Notice that the header information (from the Blog table) is repeated for each blog comment! If we had 100’s of comments and a lot of header columns, this could accumulate to several megabytes of extra space in the XML.
For that reason I still prefer to have the SQL Server produce the XML as we did with the old Adapter, since the SQL Server knows the power of the hierarchic structure in XML and is able to have the header information presented only once.
For this we use the XmlPolling option, which was created mainly for legacy purposes so that existing Applications using the old SQL Adapter can be ported and still use the “FOR XML” clause in the polling query. Unfortunately, as we shall see, it is not acting quite the same way as the old Adapter did, and therefore it is not possible to migrate directly from the old Adapter to the new.
By adding the FOR XML clause to the stored procedure again, choosing XmlPolling on the Adapter and specifying the same root name and namespace as we did with the old Adapter, we get the following XML:
At first this XML might seem identical to the XML we received from the old SQL Adapter, which would also be expected and required if we are to migrate an existing solution from the old to the new Adapter.
But if you think the XML is identical, your eyes are deceiving you! The XML produced by the old Adapter has a Schema where the namespace is derived to all the elements in the document, whereas the latter only has the namespace on the root element, and the rest just has blank namespaces.
The reason for this change is sort of a mystery and Microsoft has not described this change in any documentation I have found.
This first of all means that the documents and Schemas are totally different and to migrate a solution, you would need to change both Schemas and Maps.
What’s just as bad is the fact that if we choose to select all blogs instead of just one and then use a BizTalk Pipeline for debatching, we would get individual messages (blog) without any namespace, which is very bad practice in BizTalk!
It is possible to have the SQL Server return the same XML as produced with the old Adapter, by adding the following to the FOR XML select:
It is not pretty though, since the namespace is now redundant and has to be configured on both the Receive Location and in the SQL executed. Also we might not even be able to change the Stored Procedure, as we may not own it.
It would also be possible to create Pipeline Components that can manipulate the XML before it enters the Disassemble stage in the Pipeline and have it conformed with the old structure.
Personally I would prefer that Microsoft made the Adapters act the same way when it comes to namespaces, and since that doesn’t seem to happen, I would update the Schemas and Maps for the new Adapter and also not do any de-batching since that will cause documents with no namesapce being generated. The alternative here is to poll just one record each time the Stored Procedure is executed like the example in this blog.