Tallan's Technology Blog

Tallan's Top Technologists Share Their Thoughts on Today's Technology Challenges

Import flat file using BizTalk server

Leo Wang

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 file, build pipeline , build map, create orchestration and call web service in a BizTalk application.

Step One – Create Flat File Schema

BizTalk provides utilities to easily create flat file or XML file schema from scratch or from a wizard.

First you need to create a new flat file and save it as product.txt

1, Adjustable Race, AR-5381

2, Bearing Ball, BA-8327

3, BB Ball Bearing, BE-2349

4,Headset Ball Bearings, BE-2908

The schema for this file should be ProductId, Name, ProductNumber.

Create an empty BizTalk project, ImportProduct.

Once the project is created, right-click the project and add a new item. Select Flat File Schema Wizard in the new item window and name the file as products_FF.xsd.

In the Flat File Schema Information window, specify the location of product.txt file and record name Products.

The next screen is “Select Document Data”. Read the description in the dialog and ensure all content is selected.

Choose “By delimiter symbol” in the Select Record Format screen.

Select {CR}{LF} as Child delimiter because I used a carriage return and line feed

Once the Child Elements window appears, ensure you change the Element Name of the first row to be readable ProductItem and Element Type of the first row to Repeating Record. For other rows, you just set the Element Type to Ignore because other rows just simply repeat the first row.

The next screen basically gives you a chance to review schema and decide if I want to manually change schema or continue to configure the child element. Highlight productItem and click Next button.

Select comma in the Child delimiter of the Delimited Record screen.

Modify the Element Name, Element Type and Data Type as the following screenshot.

Right-click the Product_FF.xsd file, you can validate schema ( useful if you change schema manually ), validate instance and generate instance through the context menu.
Next, you need to create a key file to assign a strong name to the assembly. If you have a key file, you can skip this step. Open Visual Studio 2005 Command Prompt, type sn -k product.snk, then press ENTER. Then set key file in the ImportProduct project property page. Click OK to close the Property dialog.

You also need to assign application name, ImportProudct under Configuration Properties.

Right-click the ImportProduct project to build and deploy it.

If you’re using Vista like me and you get “Access is denied” error, ensure that you run the Visual Studio as the Administrator.

Step Two – Create Pipeline

After completing schema file, you need to create a pipeline to disassemble the flat file messages.
Understanding Pipeline

A pipeline is an infrastructure that is responsible to process inbound and outbound messages from the BizTalk message box. A pipeline is commonly used to parse, decode and disassemble message. Pipelines divide processing into stages. Abstractions that describe a task. Pipelines also determine the task sequence. Pipeline could be applied to receive message or send messages, where the corresponding pipeline are called receive pipeline and send pipeline. There are up to four stages for a receive pipeline, Decode, Dissemble, Validate and Resolve Party. A pipeline component is a piece of software developed using .Net or COM technology to process message inside a pipeline. You usually use Pipeline Editor utility to create pipeline.

Add a new empty project, then add receive pipeline using Receive Pipeline template.

Drag and drop the Flat File Disassembler component from the toolbox to the pipeline and drop it into the disassemble stage. This component will convert the flat message to XML message.

Add a reference to the schema project, then choose ImportProduct.FlatFileProduct in the Document schema dropdown list of the property window of the flat file disassembler

 

Build and deploy the pipeline project by following the item 13 and 14. Now you can test your pipeline by creating receive port and send port.

Create receive port using File transport type and specify the receive pipeline as FlatFileReceivePipeline.

Create send port and specify the send file location as c:\BizTalkApp\Send. You also need to specify the filter to connect the receive port and send port.
Now start the receive and send port, and drop the product.txt file in the receive location, you will notice the file is removed after a while and new file is created in the send port. Open the file, the file content is.

<Products xmlns=”http://ImportProduct.FlatFileProduct“>

<ProductItem xmlns=””>

<ProductId>1</ProductId>

<Name> Adjustable Race</Name>

<ProductNumber>AR-5381</ProductNumber>

</ProductItem>

<ProductItem xmlns=””>

<ProductId>2</ProductId>

<Name> Bearing Ball</Name>

<ProductNumber> BA-8327</ProductNumber>

</ProductItem>

<ProductItem xmlns=””>

<ProductId>3</ProductId>

<Name> BB Ball Bearing</Name>

<ProductNumber> BE-2349</ProductNumber>

</ProductItem>

<ProductItem xmlns=””>

<ProductId>4</ProductId>

<Name>Headset Ball Bearings</Name>

<ProductNumber> BE-2908</ProductNumber>

</ProductItem>

</Products>
So far so good, the flat file is converted to XML file. Next, you need to create a web service which will be called inside a BizTalk orchestration.

Step Three – Create a simple Web Service

Launch Visual Studio and create a web service application. Name the web application as ImportProductWebService.

Replace the service code with the following code, don’t forget to change the value of inherit attribute to ImportProductWebServie.ImportProductData in the web service asmx file.
namespace ImportProductWebService

{

///<summary>

/// Summary description for Service1

///</summary>

[WebService(Namespace = http://tempuri.org/)]

[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

[ToolboxItem(false)]

public class ImportProductData : System.Web.Services.WebService

{

[WebMethod]

public void UploadData(List<Product> products)

{

StringBuilder sb = new StringBuilder();

if ( products != null )

{

foreach(Product product in products)

{

sb.AppendLine(string.Format(“{0},{1},{2}”, product.ID, product.Name,product.Number));

}

}

File.WriteAllText(@”c:\BizTalkApp\uploadFile.txt”, sb.ToString());

}

public class Product{

public int ID;

public string Name;

public string Number;

}

}

}

After compiling the web service, you need to add a we reference in the importProduct schema project.

Right-click ImportProduct schema project, then click Add web reference.

Click Add Reference button, then you should see some files are created in the project.

To simply the process, the data is not actually stored into the database, instead, it will be written into uploadFile.txt file. The end result is similar to the file copy. Please note the parameter passed to the UploadData method is a List of Product type. In order to call this web service, we need to pass a message from the BizTalk to web method, therefore we need to convert the original XML message to the message which could be used to construct the a List instance during object deserialization. To convert one format XML to another format XML message, BizTalk uses Schema Map or Map.
Step Four – Build Map
Right click the ImportProduct schema project , click add new item, then choose map template and name the map file as Products_FF_To_Reference_WSRequest.btm

 

Click Open Source Schema link, then choose Products_FF.xsd, then click Open Destination Schema and choose ImportProduct.localhost.Reference and click OK.

Drag ProductId in the left pane to the ID in the right pane, then follow this screen to finish the mapping.

Step Five – Create Orchestration

After creating map, now you need to build Orchestration to transform the input message to the web service request message and call web service. Although it’s possible to call web service without using Orchestration, it would be useful when the business rules are introduced down the road.

Understanding Orchestration
Orchestration is a software application hosted by the BizTalk server. Orchestration consists of shapes. An orchestration shape is similar to a task in the Nant or Msbuild. You can create a sequence and/or parallel shapes. The commonly used shapes are Receive, Send, Transform, Construct Message, Decide, Parallel Actions and Loop. Some shapes are container shape such as Decide, Construct Message and Parallel Actions. I have borrowed all shaped
description from MSDN BizTalk document.You can create an orchestration by using Orchestration Designer, a graphical user interface tool which allows you build orchestration easier. Whenever you create a new orchestration using Visual Studio, an odx file ( XML file ) is created which holds all configuration settings and some logics to drive the BizTalk engine. In the runtime, BizTalk engine will retrieve the variables, tasks defined in the XML file to run the orchestration application.  Right-click the solution and add new empty BizTalk project, then add new item by choosing BizTalk orchestration template. Now you should see the Orchestration Designer is opened.

Before dragging and dropping the shapes from the left side pane to the Orchestration, you need to define the message parameters used in the orchestration. Click View | Other Windows | Orchestration View. In the Orchestration View, right-click Messages, then create 3 messages, product, uploadDataParamWSRequest, uploadDataparamWSResponse. Please note ussually you should name the messages, variables, orchestration parameters etc. using camel case.

Modify the property attributes Identifier and Message Type using the data value in this table.

Message name Identifier Message Type
product product ImportProduct.FlatFileProduct
uploadDataParamWSRequest uploadDataParamWSRequest ImportProduct.localhost.ImportProductData_.UploadData_request
uploadDataParamWSResponse uploadDataParamWSResponse ImportProduct.localhost.ImportProductData_.UploadData_response
Now you’re ready to design your orchestration. Right-click the Port Surface in the left side and choose the New Configured Port…, which will launch a wizard to help you configure the port easily. Once the wizard appears, click Next button, then specify the port name as ReceiveProductPort, then click Next button. In the Port Type screen, specify each item like the following screenshot.

Clicking the next button will show the Port Binding window. Ensure you select I’ll always be receiving messages on this port and choose specify later in the port binding, then click Next and Finish button to complete the receive port configuration.

Drag and drop a Receive shape under the start node in the Orchestration Designer, then specify product as message. Ensure the Activate attribute is true.
Drag and drop a Transform shape under the Receive shape, then a Construct Message container shape is automatically created. Right-click the Transform shape, then click Configure Transform shape, select ImportProduct.Product_FF_To_Rference_WSRequest as the Map, then specify the source as product, the destination as uploadDataParamWSRequest.products. The purpose of this shape to transform the input XML message to the web service request XML message. Click Ok to complete the configuration.

Drag and drop a Send shape under the Construct Message shape, then specify unploadDataParamWSRequest in the Message attribute. Drag and drop a Receive shape under the Send shape, then specify unploadDataParamWSResponse in the Message attribute. Ignore operations at this point.
Right-click the Port Surface in the right pane, then click New Configured Port…, then specify the name as SendUploadDataWsPort in the port window, click next button, then choose Request-Response in the communication pattern in the port type window, then click next.
In the Port Binding window, select I’ll be receiving a request and sending a response in the port direction of communcation , then select port binding as Specify now. Ensure select Transport as SOAP, then specify the URI as http://localhost/ImportProductWebService/ImportProductData.asmx. Then click Next, Finish button to complete the Send port configuration.

Now drag the arrow of the request in the receive port to the Receive shape, then drag the arrow of the Send shape to the request of UploadData method in the Send port, then drag the arrow of the Response of the UploadData method in the Send port to the last Receive shape. The finished orchestration looks like


 

Step Six – Build, Deploy and Test

Like any web application needed to deploy to the web server, the BizTalk application must be deployed to BizTalk server, in other word, you need to give a name for your BizTalk application. In addition to that, the BizTalk dlls are deployed to GAC, so you need to sign it using strong name.Follow the step 13 and 14 to configure other two projects in this solution. Build solution, then right-click each project to deploy them to the BizTalk server.

Open BizTalk Server Administration Console, then right-click ImportProduct application, then click start to launch your application.

If you get the orchestration binding error, you need to specify the Bindings in the Orchestration Properties windows. Ensure you select the BizTalkServerApplication as Host.

Copy the product.txt file into the C:\BizTalkApp\Receive folder. If you see the uploadfile.txt is created in the C:\BizTalkApp folder and content is exactly the same as that of the product.txt, great!, the application works.

 

Conclusion

From this simple application, you learn how to create schema, map, pipeline and orchestration in BizTalk. In addition to that, you also learn how to pass a list parameter to the web service which is very useful feature to import file. Hopefully it will also convince you that BizTalk is a powerful tool to easily build high available and scalable application.

2 Comments. Leave new

[…] recorded first by phurderick on 2008-08-03→ Import flat file using BizTalk server […]

Hi,
very nice post, but couldn’t you provide the complete version of start solution: to import data from an Excel file to SQL Server database.
thanx

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>