March 2008 - Posts

• BizTalk 2006: Default SQL Adapter – Updategrams (PART 1)

  Hi Folks, I am going to be honest here the default SQL Adapter in Microsoft BizTalk 2006 is a bit of misnomer for me. Why? If you have a look closely at the Send Handler and Receive Handler for it, you will notice the following: Send Handler As you can see above, if you want to INSERT, UPDATE or DELETE data on a SQL Table or Tables you have to provide a XML Document to do it! How? This is not the normal way that we would usually think is it? Receive Handler The above seems pretty normal to me, this is normal turf we stepping on here, this we accept with open arms. Analysis The receive handler is perfect is it not; we can actually retrieve data from a database by calling a stored procedure, and even provide some parameters. If we examine the Send Handler, the results are a bit disappointing at first glance. We cannot actually use a stored procedure here to 'push' data to SQL in a normal fashion that we would expect to. So we can pull data using a stored procedure but we cannot push data to SQL using a stored procedure directly with the default SQL Adapter, unless we use weird XML files. Watch this blog: I will be posting a blog later on how you can 'push' data to SQL using a stored procedure in a normal fashion, like the Receive Handler, by leveraging the fine features of developing a custom SQL Adapter, so no need for pain staking xml Updategrams and XML stored procedure templates, which are very limiting in certain situations. Personally I do not like the way the default adapter was implemented. Not powerful enough. Updategrams are cool for school and not really much else in my opinion, and why use XML to call a stored procedure, so much overhead, BizTalk 2006 is meant to be a screaming Ferrari not a truck out of the box. So, for the untrained eye, how do you then actually send data to SQL using the default BizTalk 2006 SQL Adapter? Well, there is three ways. Using XML Updategrams (Part 1) Using XML Store Procedure Template (Part 2) Custom SQL Adapter (Part 3) The problem is the first two require an extra step where you need to create a BizTalk Map to actually map out the Updategram or stored procedure. Before we delve deeper, let's get a bit intimate with these strange XML Documents. Remember, when I mention the word 'send' data, what I am really asking, is how do I UPDATE, INSERT and DELETE data from SQL using the Default BizTalk SQL Adapter. Imagine we have this table: USE [Test] GO /****** Object: Table [dbo].[Customer1] Script Date: 03/27/2008 00:12:26 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Customer1](     [CustomerID] [int] IDENTITY(1,1) NOT NULL,     [CustomerName] [nvarchar](30) NOT NULL,     [Address] [nvarchar](60) NULL,     [City] [nvarchar](30) NULL,     [Region] [nvarchar](30) NULL,     [PostalCode] [nvarchar](50) NULL ) ON [PRIMARY]   First let's insert a record, then update it and then delete it the Default SQL Adapter way using the UpdateGram and then we do the same using a XML Store procedure Template in Part 2. Updategram that Inserts Let's insert a record, since we have an identity field, the CustomerID will be 1 and inserted automatically. Notice the AFTER element. <ns0:Customer_Request xmlns:ns0="http://SQLAdapterUpdategram"> <ns0:sync>     <ns0:after>     <ns0:Customer CustomerName="Romiko" Address="Coffee Shop Heaven" City="Amsterdam" Region="Noord-Holland" PostalCode="13"/>      </ns0:after> </ns0:sync> </ns0:Customer_Request>   So the above is the same as: Insert into Customer1 (CustomerName, Address, City, Region, PostalCode) Values('Romiko','Coffee Shop Heaven','Amsterdam','Noord-Holland','13')   Updategram that Updates Now, let's update the record. Notice the BEFORE and AFTER. <ns0:Customer_Request xmlns:ns0="http://SQLAdapterUpdategram"> <ns0:sync> <ns0:before> <ns0:Customer CustomerID="1"/> </ns0:before> <ns0:after> <ns0:Customer CustomerName="Romiko van de dronker" /> </ns0:after> </ns0:sync> </ns0:Customer_Request>   So the above is the same as: Update Customer1 Set CustomerName='Romiko van de dronker' Where CustomerID = 1   Updategram that Deletes Ok, we getting bored of this record, lets bin it! Notice the BEFORE has the CustomerID <ns0:Customer_Request xmlns:ns0="http://SQLAdapterUpdategram"> <ns0:sync> <ns0:before> <ns0:Customer CustomerID="1"/> </ns0:before> <ns0:after> </ns0:after> </ns0:sync> </ns0:Customer_Request>   So the above is the same as: Delete from Customer1 Where CustomerID = 1   Cool, I understand them, how do I use them. Basically, in a nutshell, you create an Updategram schema and then map your source document to this Updategram. This is two ways, the manual way and the wizard way. I will show you the wizard way. To keep things consistent the schema I create below is friendly with the Updategrams I showed you above. Create a new BizTalk Project, or use an existing one Right click the project and choose 'Add Generated Items' Click Add Adapter MetaData Click Next Setup your connection string Fill in the Schema Information, entirely up to you what namespace and root element you use Choose the Type of Statement, Updategram Choose the type Click Finish You can now use this Schema to UPDATE, data from SQL. However, to do this, you will need a way to call the Updategram, we going to do this the BizTalk way, warning this is really not cool. First you will need to create a Map to map your data from the Source Document to the Schema created above. This can be tricky, because you might want to create one for an Insert, one for an Update and one for a Delete, so now you see why it is not cool. Think about it folks, we using maps and schemas and pulling out all our weapons of mass destruction in BizTalk to do a simple UPDATE,, INSERT and DELETE, not cool. One the left we have the normal Customer Schema for a record and on the right the Schema you created using the wizard (This one is an Insert Version, notice no before section). You can probably kill 3 birds with one stone and have one Updategram Schema that has both before and After in it, else the wizard makes you think you need three types, which is not the case. Just leave before or after empty etc. For simplicity I made an Insert one below only, so this schema does not support updates/deletes (No Before section). To get this all working, you will need to create a send port and in the SQL Adapter properties for this Send port you specify the schema details for the Updategram, watch: We not done! We now need to use that Map, lets really slow down BizTalk and use an Orchestration All that's left is setting up a Receive Port with your Customer Data that needs to be transformed into an UpdateGram and set up the subscriptions. Tip: You can also create a Receive Port that sucks up Updategrams directly, if you had some sort of system that generated them for you… And that's the problem the generation of them is such a mission! Conclusion I think using Updategrams or XML based store procedure calls by using the Default BizTalk SQL Adapter Send Handler, is well, not cool, and slow. Not only that, IMAGINE, you want to have a customer table where the data is stored as XML Data Type, your map would need to take the Left Hand Side XML and shove it into an attribute on the Right Side in your BizTalk Map. The best way to do this, is using XSLT and we will trod this ground in Part 2 Also in Part 2, we will find out how using the XML template stored procedure is just as slow and cumbersome and in Part 3, we will find the light at the end of the tunnel with our very own Custom SQL Adapter that works in the normal way we think it should, just the way we want it to. Why did we go through all this only to find there is a better way to do all this, well I think the best way to appreciate the nice things in life, is to first go through the not so nice experience, and this is the latter, well for me. See you in Part 2 and I hope you anticipate Part 3.                               Posted Mar 27 2008, 02:22 AM by Romiko with 2 comment(s) Filed under: BizTalk

• Automating Hosts, Host Instances and Adapter Handlers configuration in BizTalk 2006

  Hi Folks, I am going to be focusing on automating BizTalk Server 2006 configuration for the creation of Adapters, Hosts and Host Instances. The current project I am working on, we use multiple Host Instances and various send/receive adapter for the applications we have installed on BizTalk Server. Overview BizTalk has some nice built in features to make our lives easier, the following are the main features we use as BizTalk developers or administrators when deploying a clean solution. Binding Files – Automate binding your logical ports in Orchestrations to physical ports, creating send and receive ports and linking them to Adapter handlers via Host Instances. Policy Files - Business Rule Engine installation of Vocabularies, Rules and Policies. BTSNTSvc.exe.config – A place where you can add custom settings, fine tune and retrieve them programmatically, I prefer Enterprise Single Sign-On database for this, more about that in later blogs. What about Hosts, Host Instances and Adapters? Today I will be focusing on automating Host Instances and Adapter Installation and provide sample code that can be used, since it is not on the above list. Purpose This blog is to introduce you to the power of combining C# and WMI and creating a custom admin tool for your BizTalk 2006 environment, to fill in the gaps. Scenario Before we start let's focus on how one would actually go about in setting up Host Instances and Adapters. The current tool used for this is Microsoft BizTalk Server 2006 Administrative Console. Create a Host Create a Host Instance and link it to the Host Create a Send/Receive Handler for each adapter that will be using the Host So, you if you use 3 Adapters (MSMQ, File and Soap), you will have allot of work set out for you, and this can become rather tedious when rebuilding your development or test environment. So let's get down and dirty! Technical Details I am going to use a very basic example, where we want a different service process to manage Orchestrations, Send Ports and Receive Ports. So in a nutshell you can have a BizTalk Application use different host instances for hosting Send/Receive Ports and Orchestrations, for performance, since you allocate more threads to a single BizTalk application to use It is common for people to get confused with this sort of grouping, the above is for performance. Another grouping that one can do is by Application, this is more for management and deployment benefits. Microsoft has mentioned that it is efficient to sometimes group common Artifacts in BizTalk at the application level for ease of deployment. So for example your entire Schema's in one application, all Orchestrations in another and so on. WMI bts_WMINameSpace = @"root\MicrosoftBizTalkServer"; bts_HostSettingNameSpace = "MSBTS_HostSetting"; bts_ServerAppTypeNameSpace = "MSBTS_ServerHost"; bts_HostInstanceNameSpace = "MSBTS_HostInstance"; bts_AdapterSettingNameSpace = "MSBTS_AdapterSetting"; bts_ReceiveHandlerNameSpace = "MSBTS_ReceiveHandler"; bts_SendHandlerNameSpace = "MSBTS_SendHandler2"; Class Diagram Let's be honest, all you OO geeks out there can probably do some encapsulation and all the other nifty tricks to make this program GREAT! However, I have kept the code pure functional. Assumptions I have assumed your BizTalk server is in windows domain called Dev, and that you follow the best practices and use domain groups for the configuration of your Host Instances. The configuration file has the following, which you will need to change to suite your environment. username="Dev\BizTalkSVC" password="mypassword" ntgroupname="Dev\BizTalk Application Users"   The code generates a command console application, and the xml configuration file is required to be in the same directory as the executable. The command to type is BizTalkAdministration.exe and it will then look for the configuration file in the same directory. Simple, no arguments etc. Configuration File Below is a copy of the configuration file, you can see that we want to create: Four Hosts 4 Host Instances For each instance a corresponding Send or Receive Handler or both <?xml version="1.0" encoding="utf-8"?> <BtsAdminConfiguration> <Hosts> <Host hostname="Orchestrations" ntgroupname="Dev\BizTalk Application Users" isdefault="false" hosttracking="false" authtrusted="true" hosttype="1"/> <Host hostname="RecievePorts" ntgroupname="Dev\BizTalk Application Users" isdefault="false" hosttracking="false" authtrusted="true" hosttype="1"/>     <Host hostname="SendPorts" ntgroupname="Dev\BizTalk Application Users" isdefault="false" hosttracking="false" authtrusted="true" hosttype="1"/>     <Host hostname="WorkFlowEngine" ntgroupname="Dev\BizTalk Application Users" isdefault="false" hosttracking="false" authtrusted="true" hosttype="1"/> </Hosts> <HostInstances> <HostInstance servername="." hostname="Orchestrations" username="Dev\BizTalkSVC" password="mypassword" startinstance="true"/> <HostInstance servername="." hostname="RecievePorts" username="Dev\BizTalkSVC" password="mypassword" startinstance="true"/>     <HostInstance servername="." hostname="SendPorts" username="Dev\BizTalkSVC" password="mypassword" startinstance="true"/>     <HostInstance servername="." hostname="WorkFlowEngine" username="Dev\BizTalkSVC" password="mypassword" startinstance="true"/> </HostInstances> <Adapters> <Adapter name="FILE" type="FILE" comment="FILE adapter"> <ReceiveHandler hostname="Orchestrations"/> <ReceiveHandler hostname="RecievePorts"/> <ReceiveHandler hostname="WorkFlowEngine"/> <SendHandler hostname="Orchestrations"/> <SendHandler hostname="RecievePorts"/>      <SendHandler hostname="WorkFlowEngine"/> </Adapter> <Adapter name="MSMQ" type="MSMQ" comment="MSMQ adapter"> <ReceiveHandler hostname="WorkFlowEngine"/> <SendHandler hostname="WorkFlowEngine"/> </Adapter> <Adapter name="SOAP" type="SOAP" comment="SOAP adapter"> <SendHandler hostname="WorkFlowEngine"/> <SendHandler hostname="SendPorts"/> <SendHandler hostname="Orchestrations"/> </Adapter> </Adapters> </BtsAdminConfiguration>   Result Here is the result of your hard work, if you run the application with the default settings, and of course you remembered to change the account and group settings in the configuration file. Hosts Created Host Instances Created TIP: Notice the Not installed status above, this usually occurs if you provide and incorrect username and password in the configuration file, since it tries to create a windows service for you. To solve it, ensure you get your username and password right first time; else it is time to get out the helmets, elbow pads and knee pads for full contact double clicking and fixing the account credentials. File Adapters Soap Adapters MSMQ Adapters   Download Source Code The sample code for this can be found here: Developed using Microsoft Visual Studio 2005 in C#. Source Code   I hope you found this blog helpful and wish you the best of times with the new tool.     Posted Mar 24 2008, 10:33 PM by Romiko with 3 comment(s) Filed under: BizTalk