Executing multiple stored procedures from single flat file using WCF-SQL adapter RRS feed

  • Question

  • Hello,

    I am currently working with BizTalk 2013 and WCF-SQL adapter.

    I have a flat file which contains a list of names and other data with the following requirements:

    1.Delete the staging table.

    2. Insert the contents of the flat file into the staging table.

    3. Insert the data from the staging table into the Person and PersonAddress tables if the name is not in the Person table.

    4. Leave the data in the staging table if further analysis is needed by the customer.  Therefore, I cannot delete the data once requirement 3 is complete.

    I have successfully generated the flat file schema(positional), added the flat file disassembler pipeline component, generated the 3 stored procedures(sqlbinding) and mapped the flat file schema to the sql adapter's request message schema to insert into the staging table.  This part has been tested and works! So requirement 2 is done.

    I am having trouble understanding how to handle requirement 1 and 3 as they are separate from the map I've created for the flat file.  I've tested the map using orchestration successfully and thought I could work in the rest of the requirements but I'm not sure how to set the Delete and Insert(req. 3) stored procedures separate from the map used to insert into the staging table.  

    Unfortunately, I'm relatively new to BizTalk and orchestration and am having trouble completing the requirements.  I have queried for possible links but I haven't been able to find a sample that fits these type of requirements.  Your help is much appreciated.

    Thank you.

    Wednesday, January 27, 2016 6:27 PM


All replies

  • Hi Lizzie,

    Thank you for posting on MSDN forum.

    I want to ask some question for better understanding your requirement,

    1. Why you want to delete the staging table?
    2. Why not directly map/insert your FF values to Person and PersonAddress tables?

    For option #2 and #3 you can directly achieve it using SP.


    If my reply is helpful please mark as Answer or vote as Helpful.

    My blog | Twitter | LinkedIn

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Wednesday, January 27, 2016 6:54 PM
  • 1.  Why delete staging table.  There is fresh data coming in from the flat file each week(weekly process). This staging table is also used from the customer's perspective as analysis to what has been updated and what has not been updated and based on flags in the staging table why it was not updated so I cannot delete the data until the next file is loaded.

    2.  Why not directly map/insert.  In my Insert to Person and PersonAddress SP, I need to check if the "name" in the Person table exists.  If it doesn't exist, I insert the name and update staging table with flags to indicate not processed to Person table, etc. and then go to next record.

    For requirement #2 and #3 I have the SP.  I am just not sure how to handle the full workflow.

    I hope this was enough information.

    Thank you.

    Wednesday, January 27, 2016 9:23 PM
  • You can use a correlation in this case with sequential flow .

    Develop couple of stored procedure for delete ,Insert and Update Operation and the  call within your Orchestration in the sequential flow as per your requirement .

    I don't think there will be much issue once you define your process flow .




    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply

    Thursday, January 28, 2016 2:55 AM
  • Unless you have the expectation to do more processing on each 'person' in the BizTalk app, I would implement 1, 2, and 3 all in the same Stored Procedure.

    If you do have more processing in the BizTalk app, do 1 and 2 in the same SP, then Poll the staging table for each record to process into the main table.

    This TechNet Wiki Article describes some patterns you can use to read the staging table:

    • Proposed as answer by Angie Xu Thursday, February 11, 2016 8:50 AM
    • Marked as answer by Angie Xu Thursday, February 11, 2016 8:50 AM
    Thursday, January 28, 2016 2:00 PM