BizTalk Application that inserts records into SQL from *.CSV File

This article provides detailed explanation required to create a BizTalk application to retrieve CSV files from an FTP location, and update the same in SQL database.

To start with, there are 4 major steps involved.

·         Creating the table and stored procedure

·         Creating the BizTalk application

·         Configuring the ports

·         Testing the application

Creating the table and stored procedure

Note: For this sample we will be working with the Address table of AdventureWorks2000 database

Create the stored procedure as follows, to insert records to the Address table.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE insertaddress

           @AddressLine1 nvarchar(60),

           @AddressLine2 nvarchar(60),

           @City nvarchar(30),

           @PostalCode nvarchar(15),

           @Phone nvarchar(50),

           @StateProvinceID int,

           @CountryRegionCode nvarchar(3),

           @ModifiedDate datetime,

           @rowguid uniqueidentifier

AS

BEGIN

      SET NOCOUNT ON;

      INSERT INTO [AdventureWorks2000].[dbo].[Address]

           ([AddressLine1]

           ,[AddressLine2]

           ,[City]

           ,[PostalCode]

           ,[Phone]

           ,[StateProvinceID]

           ,[CountryRegionCode]

           ,[ModifiedDate]

           ,[rowguid])

     VALUES

           (@AddressLine1,

           @AddressLine2,

           @City,

           @PostalCode,

           @Phone,

           @StateProvinceID,

           @CountryRegionCode,

           @ModifiedDate,

           @rowguid)

END

GO

Creating the BizTalk Application

Open Microsoft Visual Studio 2005. Select File->New->Projects. From the Project Types: select BizTalk projects and from the right pane select Empty BizTalk Project. Provide a folder location(C :/) for the project and specify a name for the project (AddrInfoInsert) and click OK. We have to develop 5 artifacts:

·         SQL service Schema

·         Flat file Schema

·         Map

·         Receive Pipeline

·         Orchestration

Creating SQL service Schema

Right click on the newly created project and select Add->Add Generated Items. In the dialog box that appears, select Add Adapter Metadata from the left pane and Add Adapter Metadata from the templates and click ADD.

Select SQL from the list of registered adapters. Set SQL Server to the local machine and Database to BizTalkMgmtDb. Leave the port option blank and proceed.

Set the connection string. Select the SQL server name. Select “Use Windows NT Integrated Security” as log on information. Select AdventureWorks2000 from database drop down list and click Test Connection. The test connection should succeed.

In the Schema Information dialog box, provide target namespace in the following manner. http://projectname

Select send port option. Type in a unique Request and receive document root element name. (e.g. AddrRequest, AddrResponse)

Select stored procedure from Statement type information and click next. Select stored procedure from the drop down list and generate script and click next and then finish.

The adapter wizard generates an SQL Service schema.

Creating Flat file Schema

Now right click on the project name and select Add->New Item. Select Schema from the left pane and Flat File Schema from the right pane. Provide a suitable schema name and give OK. Double click the flat file schema on the solution explorer and the schema editor window appears.

On the left pane, right click on the root node and rename it (Address). Now right click on the renamed node and select Insert Schema Node->Child Record. Provide a suitable name (AddressInfo). Now right click on the child node and start adding field attributes by selecting Insert Schema Node->Child Field Attribute. Insert as many field attributes corresponding to the field attributes of SQL Service Schema and similar in naming.

Select Address from the left pane and set the following properties:

·         Child Delimiter Type : Hexadecimal

·         Child Delimiter: 0x0D 0x0A

·         Child Order: Postfix

Select AddressInfo from the left pane and set the following properties:

·         Child Delimiter Type : Character

·         Child Delimiter: ,

·         Child Order: Infix

Creating Map

Right click on project name and select Add->New Item. Select Map and provide suitable name.

In the Open Source Schema, select the flat file schema that we created. In the destination schema, select SQL Service schema and select request root from the option provided.

Go to the toolbox and from the Advanced Functoids, drag and drop the Mass Copy Functoid to the design area. Connect the functoid with AddressInfo on the left pane and stored procedure name on the right pane.

Creating Receive Pipeline

Right click on project name and select Add->New Item. Select receive pipeline and provide suitable name.

Drag and drop the Flat File Disassembler component from the list of BizTalk pipeline components into the Disassembler box in the pipeline design area.

In the property pane of Flat File Disassembler component, set the Document Schema to flat file schema created.

Save the application.

Creating Orchestration

The SQL Service generation wizard would have automatically created an orchestration. Rename it if necessary.

In the Orchestration view, create two messages with the following specifications

Message_1

·         Identifier: FF_Msg

·         Message Type: Schemas->FlatFile Schema

Message_2

·         Identifier: SQL_Msg

·         Message Type: Schemas->SQL Service Request Schema

In the Orchestration view, create two port types with the following specifications

PortType_1

·         Identifier: FF_Rcv_PortType

·         Message Type: FlatFile Schema

PortType_2

·         Identifier: SQL_Snd_PortType

·         Message Type: SQL Service Request Schema

In the Orchestration view right click on Correlation type and select new correlation type. Expand BTS and select Message Type and give Add.

Now right click on correlation set and select new correlation set. Set the correlation type to newly created correlation set.

In the Orchestration Design Window, drag and drop the following shapes and set the specific properties.

Port_1

·         Name: FF_Rcv_Port

·         Port Type: FF_Rcv_PortType

·         Port Direction: Receive

·         Port Binding: Specify later

Port_1

·         Name: SQL_Snd_Port

·         Port Type: SQL_Snd_PortType

·         Port Direction: Send

·         Port Binding: Specify later

Receive

·         Message: FF_Msg

·         Operation: FF_Rcv_Port.Operation_1.Request

·         Initializing Correlation Set: Correlation1

 

Construct Message

·         Message Constructed: SQL_Msg

Transform

Click Ellipses from the Input Messages property and from the Transform Configuration dialog box that appears, select Existing map. From Source select FF_Msg and for Destination, select SQL_Msg.

Send

·         Message: SQL_Msg

·         Operation: SQL_Snd_Port.Operation_1.Request

·         Following Correlation Set: Correlation1

Save the application. Generate a strong name key. Build and deploy the application.

Configuring the ports

Open BizTalk Administration Console. Expand the nodes and arrive at the newly created application. The orchestration appears in the admin console. Now we need to create Send and receive ports.

Creating Receive Port

Right click on Receive Port and select New->One Way Receive Port. Provide a name and select Receive location from the left pane. Select new from right pane and provide a name.

Select FTP from Type and click Configure. Browse for the receive folder location and type *.CSV in file mask.

In Receive pipeline drop down box, select the pipeline created in the application.

Creating Send Port

Right click on Send Port and select New->Static One Way Send Port. Provide a name and select SQL from Type and click Configure.

Set the connection string and test connection.

Type document namespace as earlier (http:// AddrInfoInsert)

Response Document Root Element Name: AddrResponse

Set Filters: BTS.MessageType==http:// AddrInfoInsert ## AddrInfoInsert

Configure the application and start it.

Testing the Application

Drop a *.CSV file in the receive location specified while configuring the receive port. The file is pulled by the BizTalk application and the rows will be inserted into the Address table of AdventureWorks2000 database. Check the records in the address table to confirm entry.

By Alice J   Popularity  (3150 Views)