Updategram with multiple tables with foreign keys
Updategram Overview
Since SQL Server 2000, updategrams along with OPENXML have been available to insert, update, or delete data using XML documents. An updategram works against the XML views that are provided by the annotated XSD schema that contains the information required to map to the tables and columns to be modified. Using an updategram to manipulate one table in BizTalk is very straight forward because it integrates right into the Add Generated Items wizard for the SQL Adapter. Manipulating more than one table takes a little more effort because the wizard does not allow the user to select more than one table.
Multiple table insert updategram
There are two options to create an updategram in BizTalk for multiple tables; either manually or using the wizard to generate each individual table insert updategram and then manually merging them together. The next three figures show the process of creating an updategram using the Add Generated Wizard for a single table.
You can get to the figure below by right clicking on the project and choosing Add Generated Items.
Next, you need to choose the SQL Adapter that you want to use to connect to the SQL tables and hit Next. Now, you need to add the target namespace for the updategram and specify the port type.
The next step you simply have to select updategram as the type and move on to the next step.
Now you have to select the table and select the columns that you need to insert the data into.
The figure below is the result of the updategram created from the wizard. Repeat these steps for each table that you have to insert/update.
Once you have all of the updategrams created, you can merge them into one updategram schema. Because this is an insert, all of the tables are under the after portion of the updategram. If this was an update, you would have a before record that would be used to match the records that would then be updated by the data in the after portion. The figure below displays the final result.
In order to handle identities, an additional schema is required. In the example below, it is called Updategram. The namespace has to be set to urn:schemas-microsoft-com:xml-updategram. As you can see in the figure below, there are two attributes in this schema.
• at-identity – this is used to hold the identity value for the inserted record where an identity exists on the table.
• returnid – this is used to store the value of a return value from the updategram
I am not covering the returned in this post, but I thought it might be worth a mention as it may be useful for some in the future.
In order to take advantage of the identity field mapping, you first need to add a reference to the schema that will hold all of the tables that have to be inserted. The prefix for the Updategram.xsd has to be set to updg. After you have added this reference, add an at-identity attribute to any table that you need to access the identity column.
Next, create a map and add in the source and destination schemas. The figure below contains the mapping between the parent table and the child tables with the identity reference. In order to create a mapping between the primary key of the parent table and the foreign key of the child table, you use a string concatenate functoid and specify a unique value to map between these fields. In this example I used the value HDRID. So for all tables that have to reference the identity column of the inserted header record will use the same value. The figure below has this value mapped to several tables. These are all at the header-child level.
In order to create references to the child records of each detail line, an extra step has to be taken in order to ensure that the correct value gets mapped for the relationship. The reason for this is that each child record of a detail record has to be mapped to only that record. If only DTLID was used, it would all mapped to the same value. You can see an example of this below.
< INVDTL Index=”1” at-identity=”DTLID” >
< INVDTL Index=”2” at-identity=”DTLID” >
< INVDTL Index=”3” at-identity=”DTLID” >
< INVALWDTL DetailIndex=”1” Index=”1” INVDTLID=”DTLID” >
< INVALWDTL DetailIndex=”2” Index=”2” INVDTLID=”DTLID” >
< INVALWDTL DetailIndex=”3” Index=”3” INVDTLID=”DTLID” >
The way around this is to use the string concatenate functoid and add in the iteration functoid which is configured from the detail record. This will reference the at-identity attribute to the correct field in the detail child table.
< INVDTL Index=”1” at-identity=”DTLID1” >
< INVDTL Index=”2” at-identity=”DTLID2” >
< INVDTL Index=”3” at-identity=”DTLID3” >
< INVALWDTL DetailIndex=”1” Index=”1” INVDTLID=”DTLID1” >
< INVALWDTL DetailIndex=”2” Index=”2” INVDTLID=”DTLID2” >
< INVALWDTL DetailIndex=”3” Index=”3” INVDTLID=”DTLID3” >
The figure below displays how to configure the string concatenate functoid and to complete the map for the updategram.
Now create the rest of your mappings to complete the process and deploy.
Setting up an HL7 Solution
Setting up an HL7 Solution
HL7 Accelerator Overview
As you might know, the BizTalk Accelerator for HL7 offers a great deal of enhanced messaging capabilities for the healthcare industry. Although it is offered as a separate download, it comes with the license of BizTalk. The nice part about it is that it offers all of the 2.x schemas, support for MLLP, messaging validation, batch processing, and support for advanced acknowledgments.
Steps for setting up an HL7 solution
1. First, you have to install the accelerator on BizTalk. This will install the HL7 adapters, HL7 pipelines (this is where most of the work is done), and project templates. As you might imagine, the project templates are the focus for this entry since this is all about setting up an HL7 solution.
The figure below shows all of the project templates installed through the installation process of the accelerator.
2. Select the BTAHL7V2XCommon project template, specify your directory, and hit OK. This will create the first project that will be inherited by all other HL7 projects. The reason for this is that this project includes all of the base schemas for all projects.
3. Next, Add a new project to the solution. This time you have to choose the version of HL7 that you will be working with. For this demo I will be using HL7 version 2.5 so I will choose the BTAHL7V25Common template. This project template includes all of the valid message segments, data types, and table values for the HL7 version 2.5. These schemas are based off of the standards put out by HL7.org. Any variations to the HL7 standard will have to be made to these schemas if you would like to use them in your organization. This project should reference the BTAHL7V2XCommon project.
The figure below displays the selection of the HL7 2.5 project type.
4. Now we need to add yet another project to the solution. This time we will choose an Empty BTAHL7 project. We will use this project to define the actual events that will be processed by BizTalk. This project should reference the BTAHL7V2XCommon and the BTAHL7V25Common projects.
The figure below shows the empty BTAHL7 project selection.
5. Add triggering events to the empty project. Go to Add >> New Item and choose the BTAHL7Schemas Item template.
6. Use the wizard to add the triggering events. For this example, I am going to add a check in and checkout event by choose it in the drop down and hitting create. You can add as many new events (schemas) to your project using this wizard. Just keep selecting the different types and hit create. You can see this in the figure below.
7. Finished. You can see the completed solution below. Of course you still need to deploy.
For more information on the BizTalk Accelerator for HL7, check out the following link:
http://www.microsoft.com/biztalk/en/us/accelerator-hl7.aspx
BizTalk and Systems Center: Operations Manager w/ BizTalk Management Pack
The BizTalk 2006 R2 Management Pack is a rewrite from the original pack for Microsoft Operations Manager 2005 (MOM). It is designed around all of the new features of BizTalk 2006 R2 including RFID and EDI. The pack ties into SCOM very nicely and once it has been imported into SCOM, it can automatically scan the network and will detect your BizTalk environment.
The main purpose of the pack is to
· Monitor BizTalk events
· Collect BizTalk specific performance counters in one central location
· Raise alerts on events
This pack is designed to provide the following services
· Availability Monitoring
This can detected a multitude of items with very little configuration. It will automatically detect if a send or receive port goes down, connection problems with the BizTalk database, messaging engine connection issues with the SSO database.
· Health Monitoring
You can use Health Monitoring rules to take a proactive approach to keeping your BizTalk environment running. It classifies events in familiar categories: critical error, error, warning, and information. Rules can be configured which then can be links to alerts so that someone can be notified when particular events occur. Here are just a few samples of the monitoring that you can do with the pack.
ú Error
A receive location is invalid or incorrectly configured
An adapter raised an error during message processing
BAM Interceptor detected a SQL Exception
SOAP-Receive-Message Suspended
ú Critical Error
Host instance has stopped and is not processing information
The Messaging Engine failed to register an adapter
The Messaging Engine failed to retrieve the configuration from the database
ú Warning
FILE receive adapter cannot reach a receive location due to network problems
The Messaging Engine encountered an error publishing a batch of messages
ú Information
A BizTalk Server Host Instance Windows Service Has Stopped
Mismatched Interceptor Configuration
The Messaging Engine has suspended one or more outbound message
· Utilization/Performance Tracking
· Enterprise Sign Sign On Monitoring
The bottom line is that this pack was very easy to install and to use. It is a valuable tool to have because at a glance you will know how your BizTalk environment is doing.
You can download the pack here: http://www.microsoft.com/downloads/details.aspx?FamilyId=389FCB89-F4CF-46D7-BC6E-57830D234F91&displaylang=en&displaylang=en