Create a Flat file from Stored Proc using BizTalk ?

I have a Stored Procedure on 'xyz server' , Using BizTalk I have to generate Flat file.

How can I achieve

August 25th, 2015 12:56pm

Hi,

You need to follow the below mentioned steps to achieve this scenario:

  • Import the Stored procedure into BizTalk by using "Consume Adapter Service". This will create schemas for Stored Procedure for you.
  • Create a Flat File schema indicating the format you want to achieve.
  • Create a map that will transform the SP response to Flat File structure.
  • Create a pipeline to support the flat file schema
  • In the BizTalk Admin Console, Create a WCF-Custom receive port to get the data from SQL, create a send port with FF pipeline configured and map attached.

Please indicate "Mark as Answer" or "Mark as Helpful" if this post has answered the question

Rahul

Free Windows Admin Tool Kit Click here and download it now
August 25th, 2015 1:07pm

but how will the sproc will be triggered ?
August 25th, 2015 1:10pm

Hi,

you can call a pipeline in Orchestration for converting XML to flat file.

While calling the pipeline in Orchestration, you will be use the code something similar to the following:

Microsoft.XLANGs.Pipeline.XLANGPipelineManager.ExecuteSendPipeline(typeof(YourSendPipelineWhichConvertXML2FlatFile), SendPipelineInput, YourOutputMessage);

Though the output of calling the pipeline is going to be flat file, while declaring the variable of your output message type, you have to declare the variable type as "System.Xml.XmlDocument". So in the above code "yourOutputMessage" is of the type "System.Xml.XmlDocument". For a sample, read this article where the authors uses a scenario to show how you can achieve this requirement.

https://abdulrafaysbiztalk.wordpress.com/2008/08/10/calling-send-and-receive-pipelines-from-the-orchestration-expression-shapes/

Code for the article is available here (this is not mentioned in the article)

http://abdulrafaysbiztalk.wordpress.com/files/2009/06/processingflatfiles.doc

Thanks

Abhishek


Free Windows Admin Tool Kit Click here and download it now
August 25th, 2015 1:21pm

Hello Rahul,

The Stored procedure will get triggered with the configurations(Connection string, Action Mapping) at the SQL Adapter.

August 25th, 2015 1:32pm

The Stored procedure will get triggered with the configurations(Connection string, Action Mapping) at the SQL Adapter.

Once you get the message inside the Orchestration ,it can be converted to flat file .

You can decide the approach to convert xml into flatfile using Orchestration

Thanks

Abhishek

Free Windows Admin Tool Kit Click here and download it now
August 25th, 2015 1:36pm

Hi,

Please follow below steps and refereed useful links to achieve your scenario,

1. Generate schema using WCF-SQL Adapter for BizTalk. that will be your input schema. Please refer below links for reference,

https://msdn.microsoft.com/en-us/library/ee277035(v=bts.10).aspx

How to generate schema using WCF-SQL Adapter for BizTalk

2. Create Flat file schema with flat file wizard. that will be your out put schema.

https://msdn.microsoft.com/en-us/library/aa577898.aspx

Creating Flat File schemas using the BizTalk Server Flat File Schema Wizard

3. Create a map that will transform the result of SP to flat file schema.

August 25th, 2015 1:42pm

The WCF SQL Adapter can Poll the Stored Procedure.

From your history, you've done BizTalk processes before and this is fairly easy.

What have you tried?  Are you having a specific problem?

Free Windows Admin Tool Kit Click here and download it now
August 25th, 2015 1:52pm

Hi,

you can call a pipeline in Orchestration for converting XML to flat file.

While calling the pipeline in Orchestration, you will be use the code something similar to the following:

Microsoft.XLANGs.Pipeline.XLANGPipelineManager.ExecuteSendPipeline(typeof(YourSendPipelineWhichConvertXML2FlatFile), SendPipelineInput, YourOutputMessage);

Though the output of calling the pipeline is going to be flat file, while declaring the variable of your output message type, you have to declare the variable type as "System.Xml.XmlDocument". So in the above code "yourOutputMessage" is of the type "System.Xml.XmlDocument". For a sample, read this article where the authors uses a scenario to show how you can achieve this requirement.

https://abdulrafaysbiztalk.wordpress.com/2008/08/10/calling-send-and-receive-pipelines-from-the-orchestration-expression-shapes/

Code for the article is available here (this is not mentioned in the article)

http://abdulrafaysbiztalk.wordpress.com/files/2009/06/processingflatfiles.doc

Thanks

Abhishek


August 25th, 2015 5:14pm

Hi Jaguarjags,

We can do this using pure messaging only scneario without using orchestration.

  1.        Create a stored procedure to return the data
  2.        Generate the schemas for this stored procedure using consume adapter service.
  3.        Create a schema for flat file
  4.        Create a map to convert stored procedure data to flat file
  5.        Create a custom pipeline with flat file assembler and schema bound to flat file schema
  6.        Create a wcf-Custom receive port with sql binding. Select the receive pipeline as xml receive and polling statement property set to EXEC spname
  7.        Create a send port and give the filter subscription to recieveport name. In the outbound maps select the map to convert stored proc data to flat file and pipe line as custom pipeline that you created.

Hope that helps

Cheers

Free Windows Admin Tool Kit Click here and download it now
August 25th, 2015 5:22pm

You can always specify the polling frequency on the WCF Custom Adapter to trigger the SP any no of times and generate a file every time.

There are many links on google which will provide you with enough information on how you can configure the WCF SQL adapter.

Rahul

  
August 26th, 2015 4:45am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics