This blog explains how to convert .xls to Xml file in BizTalk using Custom Pipeline.
Below pipeline component is generic one, which can be applied to any excel files for xml conversion.
Sample Input (Excel File):
Sample Output (Xml File):
Please follow below steps.
- Take a class library project and keep the code mentioned in Download link below.
2) Provide strong name key, build and deploy to GAC.
3) Take a BizTalk receive pipeline and add the pipeline component (ExcelToXmlFileConversion) to Toolbox
4) Drag and Drop the Pipeline component from toolbox to Decode stage of Receive pipeline.
5) Build BizTalk project and deploy.
6) In Admin Console, take one-way receive port and receive location.
7) In Receive pipeline, browse the pipeline “ExcelToXmlFile”
8) Provide the below information in pipeline properties.
//ConnectionString: Provide the same connection string as in the screenshot above
//DataNodeName: Root name of the repeating node
//NameSpace: BizTalk message namespace.
//SqlStatement: [Sheet1] is the sheet name in the excel sheet.
//TempDropFolderLocation: This is temporary path you need to provide to perform some internal operations by the pipeline code.
9) Take a send port and provide below properties.
10) Start the BizTalk application and restart host instances.
11) Drop an Excel file in Input location and you can see the Xml file in Output location