Get row identity from SQL Adapter response

by | Apr 18, 2007 | BizTalk Community Blogs via Syndication

Recently I had to insert a record using a stored procedure and the SQL Adapter in BizTalk 2006. There are lots of examples on both how to insert records and how to select a number of record using this adapter. However I had problems finding how to insert a record and receiving the new id of the inserted row in return (the SCOPE_IDENTITY()). In my scenario I needed the id to insert into another database further down in the orchestration.

I ended up with a stored procedure looking like the below.

<div><span style="color: #0000FF; ">ALTER</span><span style="color: #000000; "> </span><span style="color: #0000FF; ">PROCEDURE</span><span style="color: #000000; "> </span><span style="color: #FF0000; ">[</span><span style="color: #FF0000; ">dbo</span><span style="color: #FF0000; ">]</span><span style="color: #000000; ">.</span><span style="color: #FF0000; ">[</span><span style="color: #FF0000; ">TestInsertParty</span><span style="color: #FF0000; ">]</span><span style="color: #000000; ">
    </span><span style="color: #008000; ">@partyName</span><span style="color: #000000; "> </span><span style="color: #000000; font-weight: bold; ">nchar</span><span style="color: #000000; ">(</span><span style="color: #800000; font-weight: bold; ">30</span><span style="color: #000000; ">) </span><span style="color: #808080; ">=</span><span style="color: #000000; "> </span><span style="color: #0000FF; ">NULL</span><span style="color: #000000; ">
</span><span style="color: #0000FF; ">AS</span><span style="color: #000000; ">
</span><span style="color: #0000FF; ">BEGIN</span><span style="color: #000000; ">
    </span><span style="color: #0000FF; ">SET</span><span style="color: #000000; "> NOCOUNT </span><span style="color: #0000FF; ">ON</span><span style="color: #000000; ">;
    </span><span style="color: #0000FF; ">Insert</span><span style="color: #000000; "> </span><span style="color: #0000FF; ">Into</span><span style="color: #000000; "> Party (</span><span style="color: #FF0000; ">[</span><span style="color: #FF0000; ">name</span><span style="color: #FF0000; ">]</span><span style="color: #000000; ">, chain_idx) </span><span style="color: #0000FF; ">Values</span><span style="color: #000000; ">(</span><span style="color: #008000; ">@partyName</span><span style="color: #000000; ">, </span><span style="color: #0000FF; ">NULL</span><span style="color: #000000; ">)
    </span><span style="color: #0000FF; ">Select</span><span style="color: #000000; "> </span><span style="color: #FF00FF; ">Scope_Identity</span><span style="color: #000000; ">() </span><span style="color: #0000FF; ">As</span><span style="color: #000000; "> Id </span><span style="color: #0000FF; ">For</span><span style="color: #000000; "> Xml </span><span style="color: #000000; font-weight: bold; ">Raw</span><span style="color: #000000; "> (</span><span style="color: #FF0000; ">'</span><span style="color: #FF0000; ">Response</span><span style="color: #FF0000; ">'</span><span style="color: #000000; ">)
</span><span style="color: #0000FF; ">END</span></div>

The trick was to use the XML RAW Mode. This mode transforms the result set into a generic identifier as . It is however possible to provide a element name, as _. Basically this will insert the new value and return something like this from the stored procedure.

<div><span style="color: #0000FF; "><</span><span style="color: #800000; ">Response </span><span style="color: #FF0000; ">Id</span><span style="color: #0000FF; ">="1054"</span><span style="color: #FF0000; "> </span><span style="color: #0000FF; ">/></span></div>

After return via the send port the orchestration will receive something like the below.

<div><span style="color: #0000FF; "><</span><span style="color: #800000; ">TestInsertResponse </span><span style="color: #FF0000; ">xmlns</span><span style="color: #0000FF; ">="TestInsert"</span><span style="color: #0000FF; ">></span><span style="color: #000000; ">
    </span><span style="color: #0000FF; "><</span><span style="color: #800000; ">Response </span><span style="color: #FF0000; ">Id</span><span style="color: #0000FF; ">="1054"</span><span style="color: #FF0000; "> </span><span style="color: #0000FF; ">/></span><span style="color: #000000; ">
</span><span style="color: #0000FF; "></</span><span style="color: #800000; ">TestInsertResponse</span><span style="color: #0000FF; ">></span></div>

The schema that I use to both handling the response and request against the SQL Adapter is shown below. First I set the type of the Id-attribute to xs:int but this gave me some problems when using the promoted value in the orchestration, everything worked fine when switching back to xs:string.

The same technique would be used for receiving a code from the stored procedure (say 1 for success and 0 for failure or whatever) and then to make a logical decision in the orchestration.

Microsoft RFID Services – popping up in India

Microsoft RFID Services – popping up in India

by | Apr 18, 2007 | BizTalk Community Blogs via Syndication

Earlier this week I received an email from Anil Sanagavarapu who
firstly is crazy about Cricket and the World Cup.

He mentioned that his company Iris
Software is embarking on a cool RFID Services project…..

So we got chatting and I’ve just seen what they’re doing around RFID using Microsoft
RFID Services, was fantastic.

Specifically their solution implements:

  1. Sync/Async reading of Tags
  2. Enrichment of Tag data within MS RFID Services Environment, supplimenting Tag Info
    with data coming from external datasources in a high performance environment.
  3. Optional emails of various tag events as the items go about their merry way.


The guys are looking to bring out some Video Content around their solution and project
based on their experiences. They will provide a Walk through of Microsoft RFID Services,
how to develop and implement solutions.

Looking fwd to that – great work guys! smile_regular

Cheers,

Mick.

Unable to open Orchestration Designer in Visual Studio

by | Apr 17, 2007 | BizTalk Community Blogs via Syndication

We are developing BizTalk applications with Visual Studio Team Edition and every now and then for some reason the Orchestration Designer is removed from the “Open With” dialog, thus when one double clicks on an orchestration it is opened with the Xml Editor. Although I’m not sure why this happens, I suspected the items in this dialog were configured in the Windows Registry … and so they were, so to fix:

1) Open RegEdit
2) Go to HKEY_LOCAL_MACHINE\Software\Microsoft\VisualStudio\8.0\Editors and find the BizTalk Orchestration Designer for us it was under Guid {679b7fd6-2104-42b2-8d87-86dd575fc269}
3) Get the package key Guid, for us this was {2f926337-2bfb-46ab-bbc4-a955ce25ff6f}
4) Go to HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\8.0\Packages\{Package Key Guid}\SkipLoading
5) Set the SkipLoading key to 0
6) Restart Visual Studio and the Orchestration Designer should show up in the “Open With” list

Does anyone know why the SkipLoading key is mysterouslyturned onfor the Orchestration Designer?

BizTalk HotRod Magazine.

by | Apr 17, 2007 | BizTalk Community Blogs via Syndication

This morning I came across this interesting magazine via Richard Seroter. I quickly glimpsed through the magazine at work and was very impressed by the contents straight away.

 This is the first magazine I’ve seen dedicated to BizTalk resources. The first issue contains 8 good articles written by people from Microsoft and its partners.

After seeing the content of the magazine I couldn’t resist to wait till the weekend to update  BizTalk247. So, I added a new section to BizTalk247 called MAGAZINE which previews the content of BizTalk HotRod.

I encourage all BizTalk developers to go through the articles in the magazine they are well written based on real time customer situations.

As a feature article, there is an article from Stephen Kaufman which highlights the features of BizTalk 2006 R2.

Nandri!

Saravana

Consume WCF Service Wizard generates port binding configuration files for import into BizTalk Server

by | Apr 17, 2007 | BizTalk Community Blogs via Syndication

Scenario: You have already designed, implemented and hosted a WCF Service. Now, you would like to send (and get) messages to this WCF Service in a BizTalk Orchestration process.  BizTalk communicates with WCF applications via BizTalk WCF Adapter, which can be configured through send and receive ports in BizTalk.  This post talks about how BizTalk’s Consume WCF Wizard can be used to simplify the port creation process in BizTalk, especially the need of mapping the operation(s) with the SOAP Action(s) within the port to invoke the operation in WCF Service.


Microsoft BizTalk Server 2006 R2 now includes a BizTalk WCF Adapter to provide support for WS-* and transport neutrality by letting the user choose most appropriate Windows Communication Foundation (WCF) binding in BizTalk Port configuration.  The BizTalk WCF Adapter can be selected within a Send Port by creating a new port and then selecting a Transport Type.  In Receive Port, the BizTalk WCF Adapter can be selected by creating a new Receive Location and then selecting Transport Type from the drop-down box.   The Receive Location allows for hosting a non-HTTP WCF Service within BTSNtSvc.exe and can be enabled within the BizTalk Administration Console without need for publishing. 


Even though WCF-Custom is the only adapter needed, BizTalk allows other WCF adapters for most common messaging scenarios, as shown in the table below.  For example, WCF-Custom can be used as WCF-WSHttp transport by selecting WCF-Custom, clicking on Configure, select Binding tab and selecting the Binding Type wsHttpBinding or customize this transport by using wsDualHttpBinding or wsFederationHttpBinding. 

















 

Available Send Port


WCF Adapters

Available Receive Port


WCF Adapters


One-way

WCF-BasicHttp


WCF-Custom


WCF-NetMsmq


WCF-NetNamedPipe


WCF-NetTcp


WCF-WSHttp

WCF-BasicHttp


WCF-Custom


WCF-CustomIsolated


WCF-NetMsmq


WCF-NetNamedPipe


WCF-NetTcp


WCF-WSHttp


 


Two-way

WCF-BasicHttp


WCF-Custom


WCF-NetNamedPipe


WCF-NetTcp


WCF-WSHttp

WCF-BasicHttp


WCF-Custom


WCF-CustomIsolated


WCF-NetNamedPipe


WCF-NetTcp


WCF-WSHttp


Note:  Adapters built using WCF LOB Adapter SDK are consumed within BizTalk using WCF-Custom BizTalk WCF Adapter. Once the adapter is configured, it shows up in the Binding Type drop-down box and can be selected like any other predefined WCF binding.


The following section shows, at a very high level, how a BizTalk WCF Adapter is used within a typical BizTalk programming lifecycle.  


%u00b7         Design-Time Experience


o   Create a Visual Studio BizTalk Project


 


o   Use Consume WCF Service Wizard (Right Click > Add > Add Generated Items > Consume WCF Service) to download metadata from a running service using MEX endpoint or import metadata using WSDL.


%u00a7  Following files are generated as an outcome of running this wizard.


 














Metadata

XML Schema Files


Orchestration

BizTalk Orchestration (.odx) file


Port Binding Information Configuration

{ServiceName}_Custom.BindingInfo.xml – Contains port configuration using WCF Custom Adapter


 


{ServiceName}.BindingInfo.xml – Contains port configuration using other WCF bindings as obtained from the MEX endpoint


 


These configuration files can be imported in


 


%u00a7  MEX endpoints only with these transport types are supported


o   http, https, net.pipe, net.tcp


o   Implies users CANNOT use this wizard to import metadata from MEX enabled services that use a custom transport channel.  WCF users can publish and retrieve metadata over a custom binding, which may include transports (and schemes) other than the ones defined above. 


 


%u00a7  Port Binding Information Configuration Contains operation to action mapping strings


%u00b7         <BtsActionMapping> <Operation Name=”Operation1” Action=”Action1”/> </BtsActionMapping>


 


o   Build the BizTalk Project


 


%u00b7         Deployment-Time Experience


 


o   Deploy the BizTalk Project


 


o   Import the Port Binding Information XML file to auto-generate the ports in BizTalk Server 2006 Administration Console


%u00a7  Right click on Applications within BizTalk Group, select Import > Bindings and then navigate to the Visual Studio BizTalk project folder


%u00a7  Select the Port Binding Information XML file


 


o    Enlist and start the orchestration (and all its dependencies)  


 


%u00b7         Execution-Time Experience


 


o   Trigger the orchestration depending on your scenario – e.g. dropping a file in the file drop for receive location to pick it up



See the attached document for screen shots of Consume WCF Service Wizard.