Migrating to WCF SQL Adapter from SQL Adapter (Sending)

We will now look at the differences and similarities of the old SQL Adapter vs. the new WCF SQL Adapter when it comes to sending from BizTalk; both doing one-way and solicit-response communication with the SQL Server.

Sending using the old Adapter

When doing an Insert on a SQL table using the old Adapter all we have to do is send an Updategram to the SQL Adapter and set the connection string.

On the one-way SQL Send Port we are required to set both the connection string and a namespace, even though this namespace is not used for anything!! Try it yourself, the namespace do not have to match the namespace of the updategram received, it will work nonetheless. It doesn’t make sense that we have to supply a one-way Port with a namespace, it should only be required for the Solicit-Response Port when a response message requires the Adapter to set both a root element and a namespace!

01_OneWayConfig

1 Table Insert

And the updategram XML needed to populate a table (SendOneTable) with 2 rows, 4 columns would be:

02_OneWayOneTableUpdateGram

Notice that the namespace doesn’t match the namespace specified on the Adapter configuration of the Send Port at all, and the Adapter couldn’t care less!

Multiple Tables Insert

For inserting rows in several tables in the same transaction, we can use the following XML:

03_OneWayTwoTablesUpdateGram

Notice that all we did was adding another record that conforms with the other table’s name and column(s). Also notice that the new table does not reside in the default dbo Schema, but in a custom Schema (NewSchema). Even though there is a bug in the Visual Studio Schema generation tool for the SQL Adapter that do not allow us to generate Schemas for table not residing in the dbo Schema, the Adapter can update them!

04_OtherSchemaError

Calling a Stored Procedure one-way

When calling a stored Procedure named: UpdateTable with 1 parameter (textValue), the following XML is used (this time notice, that both the namespace and root element can be whatever we choose):

05_SPCallOneWay

Selecting Data using a Solicit-Response Port

Using the SQL Adapter we are not able to select directly using SQL commands, if we want data back in BizTalk. To use the Solicit-Response Port with the SQL Adapter we need to call a Stored Procedure (the SP is required to return XML (FOR XML clause).

By calling a Stored Procedure named GetTable with no input parameters, which returns a table in an XML structure:

07_Calling2WaySP

And setting up a Solicit Response Port with the following configuration:

06_TwoWayConfig(Notice that now the namespace and root element becomes relevant since they will be used for the response.)

We will get the following XML back:

08_XMLResponse

Notice that the root element and namespace was chosen by us at the Send Port level.

Sending using the new WCF Adapter.

When using the new Adapter, the syntax for inserting directly into tables are somewhat different than it was using the old Adapter:

09_WCFXMLInsert1Table

This new syntax is used both for one-way operations, solicit-response and multi-table syntax.

This means that a direct migration from the old Adapter is not possible, Schemas and maps will need to be re-generated.

When it comes to calling Stored Procedures, the syntax are closer to the old Adapter, but there are still differences:

09_WCFSPCall
So, unfortunately, unlike the receive side, which I blogged about earlier, there is no way of doing this migration without redefining Schemas and Maps.

BizTalk Tracking made easy..

Kategorier: Biztalk, Integration

Tagged as:

3 Comments »

  1. There is no way, that I’m aware of, where you can reuse Schemas/Map from the old SQL Adapter when using the new WCF-SQL Adapter on the send side. So yes I am certain to the extend of my knowledge.

  2. So Mr. Expert 🙂 (As people say)

    Can you tell me the best way to migrate all my send schema from old SQL to WCF-SQL
    Used a couple of tools but back to square one. 😦

Skriv et svar

Udfyld dine oplysninger nedenfor eller klik på et ikon for at logge ind:

WordPress.com Logo

Du kommenterer med din WordPress.com konto. Log Out / Skift )

Twitter picture

Du kommenterer med din Twitter konto. Log Out / Skift )

Facebook photo

Du kommenterer med din Facebook konto. Log Out / Skift )

Google+ photo

Du kommenterer med din Google+ konto. Log Out / Skift )

Connecting to %s