Recently I worked with a client to implement a solution to import data from an Excel file to SQL Server database. In order to provide a seamless user experience, we created a web page to allow user to upload file to server, then use BizTalk server to convert Excel file to XML file and call a web service to upload data into the SQL Server database.
By leveraging BizTalk server, we built a highly scalable and available data processing system for my client. Since my goal in this post is to introduce some core tools in the BizTalk, I simplify the real issue by 1) Changing the Excel file to the flat file, 2) uploaded data is saved into a file instead of database.
After completing this tutorial, you will be able to create flat file schema, convert flat file to XML…
Some interesting links:
BizTalk Server Database Optimization White Paper – This is an excellent MSDN write-up on how to optimize your BizTalk systems. The title is somewhat incorrect as it describes how to optimize the hardware (which RAID level to use, turn off hyperthreading), the Network (usage of subnets), the OS (Registry and other settings) , IIS and SQL Server tuning. This is an extensive and through document that includes lab performance tests and results, recommended configurations and links to helpful tools and utilities.
Some of the suggestions can be helpful even if you aren’t using BizTalk.
Enterprise Service Bus (ESB) Guidance Documentation and Source Code – This is the CodePlex community site for the latest release (CTP3) of the ESB Guidance package for BizTalk. Current plan is to release the final version in October.
Some useful webcasts to get started with BizTalk R2:
BizTalk 2006 R2 Technical Overview (Mitch Stein)
Building EDI Solutions with BizTalk Server 2006 R2 (Tony Bernard)
Orchestrating the real-time enterprise with the BizTalk RFID platform (Anush Kumar)
AS2 Adapter & EDI Business Value (Tony Bernard)
Building device providers for the BizTalk RFID platform (Anush Kumar)
Designing and deploying BizTalk RFID Business Processes (Anush Kumar)
Using BAM Interceptors for WCF and WF (Brad Paris)
Recently I was debugging an orchestration and found unexpected output from the map. The obvious decision was to use Visual Studio’s “Test Map” option. I opened the Map and realized that “Input” schema is a multi-part schema.
Combining two xml messages into one file will produce the multiple roots exception. So, I looked at the map’s source to figure out the structure. In order to view the map source, I simply ran “Validate map” and opened the generated xslt stylesheet.
The interesting part I found on the top of the schema was:
<?xml version=”1.0″ encoding=”UTF-16″?>
exclude-result-prefixes=”msxsl var s1 s0 userCSharp”
The aggregation schema
Here is the root
<xsl:apply-templates select=”/s1:Root” />
The catch statement
The Input message has to have the following structure in order to comply with the generated schema:
Here is the resulting message I came up with for testing.
You can find a dedicated page for BizTalk books on B&N!
Here is an example of a custom functoid method that converts an EBCDIC signed value to ASCII format:
Add a new C# class library project to your BizTalk solution and call it MyCompany.BizTalk.Functoids. Create a C# class in the project and use the same project name for the namespace.
/// Summary description for Utils.
public class Utils
Add the method to perform the conversion:
/// Convert EBCDIC signed values to ASCII format
/// <param name=”amount”>Amount to be converted</param>
/// <param name=”sign”>Signed Value</param>
public double ConvertFromEBCDIC(double amount, string sign)
amount = (amount / 10);
This post continues the series on creating custom pipeline components. The previous posts in the series can be found here: post 1, post 2.
In order to complete the exercise of creating the archive component, we need to add the code to actually perform the archival of the message. For this example, the requirements of the archive component is to persist an exact copy of the message coming into BizTalk (whether flat file, xml, binary or even an encrypted) in it’s original form to the local filesystem.
To start off, we need to create a method to write an inputstream to the filesystem outputstream. The following CopyStream method does this…
protected void CopyStream(Stream input, Stream output)
int BUFFER_SIZE = 4096;
byte buffer = new byte[BUFFER_SIZE];
bytesRead = input.Read(buffer, 0, BUFFER_SIZE);
I came across an article on CodeProject about creating a custom pipeline component for dissassembling incoming messages in zip formats. Even though it is implemented in 2004 version, it is still valid for 2006. It articulates well on how the custom pipeline is constructed in C# and how it can be deployed to a BizTalk solution.
One of the vexing problems when designing XML schemas for a BizTalk solution is to decide whether to promote a distinguished field or a property field. You can do both types of promotion using the Promote Property dialog box in BizTalk Schema Editor, but which one do you need?
Well, generally speaking, distinguished fields can only be used in the orchestration engine, whereas promoted property fields can be accessed through all phases of the BizTalk message processing, including orchestration, routing, pipelines, and of course custom code.
Behind the scene, the real difference lies within the implementation of these two types of promotions. For promoted property fields, BizTalk simply takes note of the XPath expression used to point to the property fields, whereas for distinguished field, the values of the fields are actually copied to the message context within the orchestration. The performance…
I was recently helping one of our clients parse a record-based flat file where the records are mostly positional identified by a tag. The issue was that the flat file was a credit card transaction log where each record, identified by a tag, is a sibling to the other records with no specific order in which the records appear. Such a scenario cannot be handled by an “out of the box” usage of the FF wizard. This series of postings will focus on explaining how BizTalk Flat File schemas are structured, analyze the output of the FF wizard and how to customize it.
BizTalk handles Flat Files through the use of flat file extensions defined within schema annotations (more about annotations…, more about flat file extensions…). A receive pipeline component known as a Flat File Disassembler (or assembler for the send counterpart) can act on these extensions to parse an…