Does anyone know of a source (article, blog, tutorial, video, ...anything) clearly describing the simplest / best practice steps required to build a BizTalk application (part) with VS being able to query a SQL Server Database table or view?
I already have a very simple but functioning BizTalk 2013 solution in Visual Studio 2012, and now I'm trying to integrate it with SQL Server 2012 the most simple way imaginable - i.e. trying to build up an orchestration with a receive port querying (either via polling or via query notification) a very simple SQL Server view.
- Of course, I have read msdn articles, all of which just describing a tiny-little part of the whole process, never getting the whole picture throughout them.
- Of course, I've also googled and searched stackoverflow around, never finding anything like that.
I'm still not sure whether you need 5 or 50 steps to achieve that (simple) goal.
This is what I could figure out. Unfortunately, this is quite a complicated process involving overall 12 steps.
You are welcome to show me a better / simpler / nicer solution, which I accept then instead of mines immediately. After all, this is why I'm here.
EDIT: + deploy + further details
Steps to query a SQL Server (2005 or above) database table / view in a BizTalk 2013 application developed in Visual Studio 2012 - the simplest / best practice approach:
- Install the BizTalk Adapter Pack. Normally, it's not installed with BizTalk together, so you have to install it separately.
- Ensure that the Service Broker is active in your SQL Server.
- Ensure that your BizTalk application has the necessary permissions to request notification in your SQL Server database. See: Enabling Query Notifications.
- In your BizTalk project in Visual Studio, generate a WCF-SQL BizTalk adapter notification schema via the Add Adapter Metadata Wizard and configure it as described here: Processing Notification Messages to Perform Specific Tasks. For the notification, you are only allowed to query a table - even if you need the result set of a view. In that case, query (one of) the underlying table(s) here.
- Add a notification message of the just generated notification schema to your just generated orchestration.
- Add a receive port of the just generated port type and a receive shape to the orchestration to receive the notification messages. And there you have your SQL notification message in your orchestration.
- Generate a WCF-SQL BizTalk adapter select schema via the Add Adapter Metadata Wizard similarly as in step 4. Don't choose any existing adapter in this step, and don't configure any binding - just take the URI you created in step 4 with another inbound ID. Select the view / table you want to query and the select operation from the available ones.
- Add a select query and a select response message of the just generated respective schemes to the orchestration.
- Add a send-receive port of the respective send and receive shapes to the orchestration to send out the select query messages and receive the result messages back. And there you have your SQL query result in your orchestration. Process it as you like.
- Build and deploy your BizTalk project in Visual Studio.
- Configure your just deployed BizTalk application via BizTalk Server Administration. As a first step, add the WCF-SQL adapter to the list of adapters in your BizTalk Server. It's a good idea to export your binding configuration here, once you have fought your way through it, so that you can later import it back if you loose it e.g. because of a new deployment.
- Start the application.
And there you have your running BizTalk application querying a SQL Server database table / view.
Menu
794
