Hi Folks,

I am going to be honest here the default SQL Adapter in Microsoft BizTalk 2006 is a bit of misnomer for me.

Why? If you have a look closely at the Send Handler and Receive Handler for it, you will notice the following:

Send Handler

As you can see above, if you want to INSERT, UPDATE or DELETE data on a SQL Table or Tables you have to provide a XML Document to do it! How? This is not the normal way that we would usually think is it?

Receive Handler

The above seems pretty normal to me, this is normal turf we stepping on here, this we accept with open arms.

Analysis

The receive handler is perfect is it not; we can actually retrieve data from a database by calling a stored procedure, and even provide some parameters.
If we examine the Send Handler, the results are a bit disappointing at first glance. We cannot actually use a stored procedure here to ‘push’ data to SQL in a normal fashion that we would expect to. So we can pull data using a stored procedure but we cannot push data to SQL using a stored procedure directly with the default SQL Adapter, unless we use weird XML files.

Watch this blog: I will be posting a blog later on how you can ‘push’ data to SQL using a stored procedure in a normal fashion, like the Receive Handler, by leveraging the fine features of developing a custom SQL Adapter, so no need for pain staking xml Updategrams and XML stored procedure templates, which are very limiting in certain situations. Personally I do not like the way the default adapter was implemented. Not powerful enough. Updategrams are cool for school and not really much else in my opinion, and why use XML to call a stored procedure, so much overhead, BizTalk 2006 is meant to be a screaming Ferrari not a truck out of the box.

So, for the untrained eye, how do you then actually send data to SQL using the default BizTalk 2006 SQL Adapter?
Well, there is three ways.

• Using XML Updategrams (Part 1)
• Using XML Store Procedure Template (Part 2)
• Custom SQL Adapter (Part 3)

The problem is the first two require an extra step where you need to create a BizTalk Map to actually map out the Updategram or stored procedure.

Before we delve deeper, let’s get a bit intimate with these strange XML Documents. Remember, when I mention the word ‘send’ data, what I am really asking, is how do I UPDATE, INSERT and DELETE data from SQL using the Default BizTalk SQL Adapter.

Imagine we have this table:

 

First let’s insert a record, then update it and then delete it the Default SQL Adapter way using the UpdateGram and then we do the same using a XML Store procedure Template in Part 2.

Updategram that Inserts

Let’s insert a record, since we have an identity field, the CustomerID will be 1 and inserted automatically.

Notice the AFTER element.

 

So the above is the same as:

 

Updategram that Updates

Now, let’s update the record. Notice the BEFORE and AFTER.

 

So the above is the same as:

 

Updategram that Deletes

Ok, we getting bored of this record, lets bin it! Notice the BEFORE has the CustomerID

 

So the above is the same as:

 

Cool, I understand them, how do I use them.

Basically, in a nutshell, you create an Updategram schema and then map your source document to this Updategram.

This is two ways, the manual way and the wizard way. I will show you the wizard way. To keep things consistent the schema I create below is friendly with the Updategrams I showed you above.

• Create a new BizTalk Project, or use an existing one
• Right click the project and choose ‘Add Generated Items’

• Click Add Adapter MetaData

• Click Next

• Setup your connection string

• Fill in the Schema Information, entirely up to you what namespace and root element you use

• Choose the Type of Statement, Updategram

  

• Choose the type

• Click Finish

You can now use this Schema to UPDATE, data from SQL. However, to do this, you will need a way to call the Updategram, we going to do this the BizTalk way, warning this is really not cool.

• First you will need to create a Map to map your data from the Source Document to the Schema created above. This can be tricky, because you might want to create one for an Insert, one for an Update and one for a Delete, so now you see why it is not cool. Think about it folks, we using maps and schemas and pulling out all our weapons of mass destruction in BizTalk to do a simple UPDATE,, INSERT and DELETE, not cool.

One the left we have the normal Customer Schema for a record and on the right the Schema you created using the wizard (This one is an Insert Version, notice no before section). You can probably kill 3 birds with one stone and have one Updategram Schema that has both before and After in it, else the wizard makes you think you need three types, which is not the case. Just leave before or after empty etc. For simplicity I made an Insert one below only, so this schema does not support updates/deletes (No Before section).

• To get this all working, you will need to create a send port and in the SQL Adapter properties for this Send port you specify the schema details for the Updategram, watch:

• We not done! We now need to use that Map, lets really slow down BizTalk and use an Orchestration

• All that’s left is setting up a Receive Port with your Customer Data that needs to be transformed into an UpdateGram and set up the subscriptions.

Tip: You can also create a Receive Port that sucks up Updategrams directly, if you had some sort of system that generated them for you… And that’s the problem the generation of them is such a mission!

Conclusion

I think using Updategrams or XML based store procedure calls by using the Default BizTalk SQL Adapter Send Handler, is well, not cool, and slow.

Not only that, IMAGINE, you want to have a customer table where the data is stored as XML Data Type, your map would need to take the Left Hand Side XML and shove it into an attribute on the Right Side in your BizTalk Map. The best way to do this, is using XSLT and we will trod this ground in Part 2

Also in Part 2, we will find out how using the XML template stored procedure is just as slow and cumbersome and in Part 3, we will find the light at the end of the tunnel with our very own Custom SQL Adapter that works in the normal way we think it should, just the way we want it to.

Why did we go through all this only to find there is a better way to do all this, well I think the best way to appreciate the nice things in life, is to first go through the not so nice experience, and this is the latter, well for me. See you in Part 2 and I hope you anticipate Part 3.

 

Note WCF solved all these issues here!

• Uncategorized