none
Is it possible handle multiple sp's in single orchestration Biztalk server 2013 R2 RRS feed

  • Question

  • Hi,

    i have a package which is having multiple stored procedures say 4 procs. can i handle all the sps in single orchestration or do i need to create orchestration for each sps? 

    what about the receive location/service url? can i handle it in one and do i need to create separate Receive port for each? both input and output is in json format.

    __________________________

    Thanks,

    Rajeshwari M.L

    Thursday, June 16, 2016 10:05 AM

Answers

  • Hi Rajeshwari,

    You cannot configure the json receive pipeline with JSON Decoder for multiple Root nodes and namespaces. There is no straightforward way to apply different rootnodes/namespaces on different JSON messages over the same ReceiveLocation. You need to have different receive ports and receive locations for your requirement.

    Yes, single orchestration can execute multiple stored procedures but as stated above you will have to use different receive locations so it is recommend that you use separate orchestration for each process. Refrain to get into extra complexity.

    FYI, for executing multiple stored procedures follow the below process:

    After right-clicking the BizTalk project in Visual Studio.NET, and selecting the Add Generated Items and then Consume Adapter Service, the LOB browser is launched. You can select “procedures” artifacts from the Oracle table.

    The wizard produced both a schema file containing all the parameters for the procedures, and a single binding file that had the send port details needed by both procedures. You should use the same binding file while setting up the application in BizTalk console. You can set multiple operations on the same Logical Send Port in your orchestration.


    These operation gets promoted to  BTS.Operation context property which at runtime refer to the Operation name in BtsActionMapping section and execute the particular stored procedure.

    You can also execute multiple operations with the Composite operation.

    Oracle Composite Operations: https://msdn.microsoft.com/en-us/library/dd788415.aspx?f=255&MSPPError=-2147217396

    More details and example of Composite Operations: http://social.technet.microsoft.com/wiki/contents/articles/26279.biztalk-streamlining-wcf-sql-and-oracle-messaging-only-and-other-patterns.aspx


    Rachit Sikroria (Microsoft Azure MVP)

    Thursday, June 16, 2016 10:16 AM
    Moderator
  • Hi Rajeshwari

    As discussed the other day on one of your earlier threads, since you need the JSONDecoder on the Receive pipeline to generate the XML format message, you will need different ReceivePorts/ReceiveLocations, since you are receiving different JSON formats. The reason for this is that the pipeline configuration needs the RootNode and Namespace properties - so a single pipeline/ReceiveLocation can generate a single type of XML document.

    Regarding the orchestration query, a single orchestration can certainly invoke multiple SPs on the same database server. This can be done using multiple operations on the same Logical Send Port in orchestration designer. See the logical SendPort below, there are 3 operations declared on it (each can invoke a different Stored Proc)-

    The orchestration operation gets promoted in BTS.Operation which can then be mapped in the SendPort to a particular Stored Proc Action via BtsActionMapping section. If you run the wizard to generate the schemas and binding files, the binding file will correctly create the multi-SP BtsActionMapping section for you as below-

    But in your case, since you also have to send back the Oracle response via the same ReceivePort, it is probably better to have different orchestrations in your scenario.

    Note, technically it is possible to have a single orchestration. The flow in that case will be - Multiple Receive Locations (all generated by the wizard). Move all of them to a single ReceivePort. The orch will be bound to this ReceivePort. The orch input message will be XmlDocument type so that any message can be received by it. Now in the orch, you have to determine messageType(BTS.MessageType property) and dynamically apply map to your Oracle request schema. Once you get back Oracle response, again determine MessageType and dynamically apply map on outbound side(optional). Send back JSON response via JSONEncoder pipeline.


    Thanks Arindam









    Thursday, June 16, 2016 10:10 AM
    Moderator

All replies

  • Hi Rajeshwari

    As discussed the other day on one of your earlier threads, since you need the JSONDecoder on the Receive pipeline to generate the XML format message, you will need different ReceivePorts/ReceiveLocations, since you are receiving different JSON formats. The reason for this is that the pipeline configuration needs the RootNode and Namespace properties - so a single pipeline/ReceiveLocation can generate a single type of XML document.

    Regarding the orchestration query, a single orchestration can certainly invoke multiple SPs on the same database server. This can be done using multiple operations on the same Logical Send Port in orchestration designer. See the logical SendPort below, there are 3 operations declared on it (each can invoke a different Stored Proc)-

    The orchestration operation gets promoted in BTS.Operation which can then be mapped in the SendPort to a particular Stored Proc Action via BtsActionMapping section. If you run the wizard to generate the schemas and binding files, the binding file will correctly create the multi-SP BtsActionMapping section for you as below-

    But in your case, since you also have to send back the Oracle response via the same ReceivePort, it is probably better to have different orchestrations in your scenario.

    Note, technically it is possible to have a single orchestration. The flow in that case will be - Multiple Receive Locations (all generated by the wizard). Move all of them to a single ReceivePort. The orch will be bound to this ReceivePort. The orch input message will be XmlDocument type so that any message can be received by it. Now in the orch, you have to determine messageType(BTS.MessageType property) and dynamically apply map to your Oracle request schema. Once you get back Oracle response, again determine MessageType and dynamically apply map on outbound side(optional). Send back JSON response via JSONEncoder pipeline.


    Thanks Arindam









    Thursday, June 16, 2016 10:10 AM
    Moderator
  • Hi Rajeshwari,

    You cannot configure the json receive pipeline with JSON Decoder for multiple Root nodes and namespaces. There is no straightforward way to apply different rootnodes/namespaces on different JSON messages over the same ReceiveLocation. You need to have different receive ports and receive locations for your requirement.

    Yes, single orchestration can execute multiple stored procedures but as stated above you will have to use different receive locations so it is recommend that you use separate orchestration for each process. Refrain to get into extra complexity.

    FYI, for executing multiple stored procedures follow the below process:

    After right-clicking the BizTalk project in Visual Studio.NET, and selecting the Add Generated Items and then Consume Adapter Service, the LOB browser is launched. You can select “procedures” artifacts from the Oracle table.

    The wizard produced both a schema file containing all the parameters for the procedures, and a single binding file that had the send port details needed by both procedures. You should use the same binding file while setting up the application in BizTalk console. You can set multiple operations on the same Logical Send Port in your orchestration.


    These operation gets promoted to  BTS.Operation context property which at runtime refer to the Operation name in BtsActionMapping section and execute the particular stored procedure.

    You can also execute multiple operations with the Composite operation.

    Oracle Composite Operations: https://msdn.microsoft.com/en-us/library/dd788415.aspx?f=255&MSPPError=-2147217396

    More details and example of Composite Operations: http://social.technet.microsoft.com/wiki/contents/articles/26279.biztalk-streamlining-wcf-sql-and-oracle-messaging-only-and-other-patterns.aspx


    Rachit Sikroria (Microsoft Azure MVP)

    Thursday, June 16, 2016 10:16 AM
    Moderator