How to generate schema using WCF-SQL Adapter for BizTalk 2009

The WCF-SQL Adapter allows generating schema using Stored Procedure via 2 different ways.

1. “Add Generated Items”.”Add Adapter Metadata”

2. “Add Generated Items”.”Consume Adapter Service”

Both options produce different results and I’ll walkthrough both options and will mention the differences.

Generate Schema Using “Add Adapter Metadata”

Right click on BizTalk Project and select “Add Generated Items”

Select “WCF-SQL” and click Next

Click “Next” and it will display the following window “Consume Adapter Service”

a. Select binding “sqlBinding”

b. Configure the URI: “mssql://<servername>//<databasename>

a. For example“mssql://localhost//GlobalBankESB”

c. Select a category:
Notice, the list contains “Procedures” and “Strongly Typed Procedures” If “Procedures” is chosen then the following artifacts will be generated:

1. BizTalk Orchestration.odx

2. sqlBinding_dbo.xsd

3. sqlBinding_System_x2eData.xsd

4.

What each artifact is used for?

1. “BizTalk Orchestration.odx”

a. Open the “BizTalk Orchestration.odx” and go to Orchestration View. Expand the Types and you will notice the wizard generated Port Types and Multi-part Message Types. This is important as you may use the preconfigured Port Type in your orchestration to send and receive messages to/from SQL database

2. “sqlBinding_dbo.xsd”

a.

b. Notice: The response object is not strongly typed. If you want the response object to be strongly typed then you must select “Strongly-Typed Procedures” from the “Consume Adapter Service” page, under “Select a Category”.

3. “sqlBinding_System-x2eData.xsd”

a.

b. This defines the DataSet structure used in the “sqlBinding_dbo.xsd”

d. Select a category:
Notice, the list contains “Procedures” and “Strongly Typed Procedures” If “Strongly Typed Procedures” is chosen then the following artifacts will be generated:

1. BizTalk Orchestration.odx

2. sqlBinding_dbo.xsd

3. sqlBinding_GetOrderDetails.xsd

4.

5. NOTICE: If Procedures is chosen, then It generates “sqlBinding_System-x2eData.xsd” and if Strongly Typed Procedures is chosen then it generates “sqlBinding_GetOrderDetails.xsd”. This is a strongly typed schema that contains all the column names the stored procedure will return.

What does each artifact contain?

1. “sqlBinding_dbo.xsd”

a.

i. Notice: the response object contains all the column names the stored procedure will return.

2. “sqlBinding_GetOrderDetails.xsd”

a.

3. “BizTalk Orchestration.odx”

a. Open the “BizTalk Orchestration.odx” and go to Orchestration View. Expand the Types and you will notice the wizard generated Port Types and Multi-part Message Types. This is important as you may use the preconfigured Port Type in your orchestration to send and receive messages to/from SQL database

Generate Schema Using “Consume Adapter Service”

Right click on BizTalk Project and select “Add Generated Items”

Click “Add” and it will display the following window “Consume Adapter Service”

e. Select binding “sqlBinding”

f. Configure the URI: “mssql://<servername>//<databasename>

a. For example“mssql://localhost//GlobalBankESB”

g. Select a category:
Notice, the list contains “Procedures” and “Strongly Typed Procedures” If “Procedures” is chosen then the following artifacts will be generated:

1. DataSetSchema.xsd

2. Procedure.dbo.xsd

3. WcfSendPort_SqlAdapterBinding_Custom.bindinginfo.xml

4.

What each artifact contains?

a. “Procedure.dbo.xsd”

i.

b. DataSetSchema.xsd

i.

c. WcfSendPort_SqlAdapterBinding_Custom.bindinginfo.xml

i. You may use this binding file to generate the Ports. This is useful when you need to create a static port. This binding files is not needed when using ESB2.0 which uses dynamic port.

Now Which option should I choose to generate the Schema?

If using ESB2.0 which uses dynamic ports then use “Add Adapter Metadata” option to generate SQL schema. This will produce the .odx file which will contain the Port Types and Multi-Part Messages which could be used in orchestration to create Messages and Port Type.