Thursday, November 22, 2012

WCF SQL operations



When adding WCF sql ports to BizTalk, you can add a whole host of different types of operation with SQL and it’s incredibly powerful. 

The problem is with all these options, the wizard rarely makes a binding file for you, it will make the schemas, but the binding file contains that extra little bit of magic…

The key to all of this in the SOAP action header you specify on the port.

There are your basic ones:

TableOp/Insert/dbo/Employee
TableOp/Update/dbo/Employee

If you read this, it will do a table operation and insert or update employee record(s) in the employee table.

Then there is of course stored procedures for both send and receive ports. 

TypedProcedure/dbo/StoredProcedureName

Simple enough, however maybe you have a few operations you want to do, you want to go for a more composite operation. 

Your Soap action can be CompositeOperation

The message you send can do a whole host of things, for example, insert into a table, and when done, run a stored procedure.

You have your insert schema, and your execute stored procedure schema, that the wizard generates, now bundle this into the following structure and all of a sudden a whole world of opportunities opens up.

<xs:element name="Request">
    <xs:complexType>
      <xs:sequence>
        <xs:element ref="ns0:Insert" />
        <xs:element ref="ns1:CompareEmployee" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
  <xs:element name="RequestResponse">
   <xs:complexType>
    <xs:sequence>
     <xs:element ref="ns0:InsertResponse" />
     <xs:element ref="ns1:CompareEmployeeResponse"/>
    </xs:sequence>
   </xs:complexType>
  </xs:element>

Then there are the Generic operations, vastly undocumented, for example a SQL reader, that can execute SQL against the database and return the results of the query…

Your Soap Action header would be: GenericOp/ExecuteReader

The schema looks like this:
  <ExecuteReader xmlns="http://schemas.microsoft.com/Sql/2008/05/GenericTableOp/">
    <Query>[PL/SQL STATEMENT1];[PL/SQL STATEMENT2];…</Query>
  </ExecuteReader>


Have a look at some of the more unknown options available, and you start to see the power in all of this. 

Operation
Soap Action  Header
ExecuteNonQuery Request
GenericOp/ExecuteNonQuery
ExecuteNonQuery Response
GenericOp/ExecuteNonQuery/response
ExecuteReader Request
GenericOp/ExecuteReader
ExecuteReader Response
GenericOp/ExecuteReader/response
ExecuteScalar Request
GenericOp/ExecuteScalar
ExecuteScalar Response
GenericOp/ExecuteScalar/response

Refer to this link for more: http://msdn.microsoft.com/en-us/library/dd788372%28v=bts.10%29.aspx

Monday, November 5, 2012

BizTalk 2013 *Beta* Released

BizTalk has existed in one form or another since the early Site Server Days, then BizTalk 2000-2002 and then BizTalk 2004, and now all the way to 2013.... Welcome BizTalk Server 2013 (previously called BizTalk Server 2010 R2)

I have been using it as part of the TAP, it still rocks like BizTalk always does, but now it's great in the cloud.... finally. 

It is available for download at Microsoft Download Center.

BizTalk Server 2013 Beta offers significant enhancements to the already rich integration capabilities by including the following feature additions:
  • Integration with Cloud Services: BizTalk Server 2013 Beta includes new out-of-the box adapters to send and receive messages from Windows Azure Service Bus. It also provides capabilities to transfer messages using different relay endpoints hosted on Azure.
    • SB-Messaging adapter: This version introduces the SB-Messaging adapter to send and receive messages from Service Bus entities like Queues, Topics, and Relays. You can use the SB-Messaging adapters to bridge the connectivity between Windows Azure and on-premises BizTalk Server, thereby enabling users to create a typical hybrid application (read more here).
    • WCF-BasicHttpRelay Adapter: Microsoft BizTalk Server uses the WCF-BasicHttpRelay adapter when receiving and sending WCF service requests through the BasicHttpRelayBinding. The WCF-BasicHttpRelay adapter enables you to send and receive messages from the Service Bus relay endpoints using the BasicHttpRelayBinding (read more here).
    • WCF-NetTcpRelay Adapter: Microsoft BizTalk Server uses the WCF-NetTcpRelay adapter when receiving and sending WCF service requests through the NetTcpRelayBinding (read more here).
  • RESTful services: BizTalk Server 2013 Beta provides adapters to invoke REST endpoints as well as expose BizTalk Server artifacts as a RESTful service.
    • WCF-WebHttp Adapter: Microsoft BizTalk Server uses the WCF-WebHttp adapter to send messages to RESTful services. The WCF-WebHttp send adapter sends HTTP messages to a service from a BizTalk message. The receive location receives messages from a RESTful service. For GET and DELETE request, the adapter does not use any payload. For POST and PUT request, the adapter uses the BizTalk message body part to the HTTP content/payload (read more here).
  • Enhanced SharePoint adapter: Integrating with SharePoint using BizTalk Server 2013 Beta is now as simple as integrating with a file share. We have removed the need for dependency on SharePoint farms, while still providing backward compatibility (read more here).
  • SFTP adapter: After Microsoft, in 2009, improved the FTP adapter by extended to support FTPS, BizTalk Server 2013 Beta enables sending and receiving messages from a secure FTP server using the SSH file transfer protocol (read more here).
  • ESB Toolkit integration: With BizTalk Server 2013 Beta, ESB Toolkit is now fully integrated with BizTalk Server. Also, the ESB Toolkit configuration experience is vastly simplified to enable a quick setup (read more here).
  • Dependency tracking: A typical BizTalk Server application involves various artifacts such as orchestrations, send ports, receive locations, pipelines, schemas, maps, and the likes. All these artifacts have dependencies on each other. The dependencies between artifacts can now be viewed and navigated in Admin console (read more here).
  • Improvements in dynamic send ports: BizTalk Server 2013 Beta provides the ability to set host handler per adapter, instead of always using the default send handler of the adapters (read more here).
  • Updates to the supported EDI schemas: BizTalk Server 2013 introduces support for the following EDI schemas:
    • X12: 5040, 5050, 6020, 6030
    • EDIFACT: D06A, D06B, D07A, D07B, D08A, D08B, D09A, D09B, D10A, D10B
  • XslCompiledTransform: BizTalk Server mapping engine makes use of XslTransform API for mapping needs. With BizTalk Server 2013 Beta release, the mapping engine makes use of the enhanced XslCompiledTransform API. This will provide improvements in mapping engine performance.
  • Ordered Send Port improvements: With BizTalk Server 2013 release, we have made changes to the BizTalk runtime engine which increases the performance of ordered send port scenarios.
  • BAM Alerts update: In previous releases of BizTalk Server, BAM Alerts feature had a dependency on SSNS (SQL Server Notification Services). With the current release of SQL Server (SQL Server 2012), SSNS is no longer available. However, we have made sure your existing BAM Alerts scenario work just the same even if your backend is targeting SQL Server 2012. If your backend is SQL Server 2008 R2, you will continue to require the dependency on SSNS.
System Requirements
  • Supported Operating Systems: Windows Server 2008 R2 SP1, Windows Server 2012
The software is for evaluation and deployment planning purposes only. If you plan to install the software on your primary computer, we recommend that you back up your existing data prior to installation.
To run BizTalk Server 2013 Beta you need:
  • 32-bit (x86) platforms: Computer with an Intel Pentium-compatible CPU that is 1 GHz or faster for single processors; 900 MHz or faster for double processors; or 700 MHz or faster for quad processors
  • 64-bit (x64) platforms: Computer with a CPU that is compatible with the AMD64 and Extended Memory 64-bit Technology (EMT64T), 1.7 GHz or faster processor recommended for BizTalk Server 2013 Beta
  • 2 GB of RAM minimum (more recommended)
  • 10 GB of available hard-disk space
  • VGA monitor (1024 x 768) or higher-resolution monitor
  • Microsoft Mouse or compatible pointing device
To use BizTalk Server 2013 Beta you need the following software:
  • SQL Server 2012 or SQL Server 2008 R2 SP1
  • Microsoft .NET Framework 4.5
  • Microsoft Visual Studio 2012 [Required for selected features only]
  • Microsoft Office Excel 2010 [Required for selected features only]
  • SQL Server 2005 Notification Service [Required for selected features only]
  • SQLXML 4.0 with Service Pack 1 [Required for selected features only]
  • Internet Information Services (IIS) Version 7.5 and 7.0 [Required for selected features only]
All these capabilities will be available in Windows Azure Virtual Machine platform gallery soon.