Insert FlatFile Data into sql Table using Biztalk WCF-SQL Adapter

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 Stored procedure  in a BizTalk application.

Step One Create Flat File Schema

BizTalk provides utilities to easily create flat file or XML file schema.

Create an empty BizTalk project,

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 authors_FF.xsd.

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

Sample File for Author(author.txt)-

LastName,FirstName,Phone,Address,City,State,Zip,Contract

LastName,FirstName,Phone,Address,City,State,Zip,Contract

LastName,FirstName,Phone,Address,City,State,Zip,Contract

LastName,FirstName,Phone,Address,City,State,Zip,Contract

LastName,FirstName,Phone,Address,City,State,Zip,Contract

LastName,FirstName,Phone,Address,City,State,Zip,Contract

·  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  authorDetail  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.

Then define the record elements by define delimiter as ','.

Step Two Create Pipeline

·          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.

·          Then choose Author Schema  in the Document schema dropdown list of the property window of the flat file disassembler

Step 3 , Create Stored procedure for insert author data to author table .

Create procedure USP_INS_Authors

@au_lname varchar(20),

@au_fname varchar(20),

@phone char(12),

@address varchar(40),

@city varchar(20),

@state  char(2),

@zip char(5),

@contract  bit

AS

BEGIN

INSERT INTO [pubs].[dbo].[authors]

           ([au_lname]

           ,[au_fname]

           ,[phone]

           ,[address]

           ,[city]

           ,[state]

           ,[zip]

           ,[contract])

     VALUES

           (@au_lname,@au_fname,@phone,@address,@city,@state,@zip,@contract);

END

Create Schema for Stored procedure “USP_INS_Authors“ , using wcf-sql adapter 

Right Click Project , add--> Add Generated Items--> Consume Adapter service

Configure URI

Create Map for transform flatfile to storedprocedure xml.

Select Input as flatfile schema and output as Stored procedure schema

Create Orchestration for receive flatfile , transform that flatfile to xml and send data to data table using wcf-sql

 

Right Click your project and deploy application