locked
How to insert multiple tables? RRS feed

  • Question

  • Hi there,

    I create a logic apps to receive EDI file.

    I want to process this file insert data to multiple tables in  SQL server.

    How to do this?

    Thanks in advance.

    Wednesday, September 19, 2018 6:23 AM

Answers

All replies

  • Hi,

    You can,

    1) Use EDIFACT Decoder logic app connector (Refer Link#1)to decode the incoming EDI message then

    2) Use the Transform XML connector (Refer Link#2)to Transform and route values from EDI to SQL equivalent message and then

    3) Use On-Premise SQL server using the Azure Data Gateway Service (Refer Link#3)and

    4) Call the Store procedure to Insert values to multiple tables on On-Premise SQL Server(Refer Link#4).

    Link#1 https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-enterprise-integration-edifact-decode

    Link#2 https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-enterprise-integration-transform

    Link#3 https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-gateway-connection

    Link#4 https://docs.microsoft.com/en-us/azure/connectors/connectors-create-api-sqlazure

    HTH


    Hope this Helps!!!! Regards, Note: Please Mark As Answered if you satisfy with Reply.

    Wednesday, September 19, 2018 12:59 PM
  • Hi vikas.a.mehta,

    Is there an Ation like BizTalk Adapter in Logic Apps?
    Can write multiple tables at once through XML Schema.

    Thanks in advance.

    Thursday, September 20, 2018 1:34 AM
  • Hi there,

    Follow the steps below as a outline to process X12 EDI files using Logic Apps:

    1. Create Integration account, define Parties and Agreement. While creating Agreement, the minimal plan lets you have one 1 agreement only. The charges increase substantially when you move to the next pricing plan. Be aware of this for your test agreements. We share one agreement for our DEV and TEST environments.

    2. Add your interface details to the send/receive settings. For example, if you have configured your id as the host party and you receive EDI 834 files (Healthcare claim) from the guest party, you have to provide the identifier details etc. in the receive side blade of the agreement screen. Otherwise you will get Schema not found error when you run the Logic App action to decode the file. The outputs of the decode action will also contain the auto generated 997's if you have enabled that in the agreement. You will can receive 997 which will go into the ReceivedAcks array of the decode action output.

    3. Mostly, you would receive EDI file from an SFTP or FTP location. Don't place the trigger on SFTP locations, we never managed to get the trigger invoked on a consistent basis. Instead, use a scheduled trigger to poll the SFTP folder periodically.

    4. Next, you can use the EDI decode by agreement action as Vikas has mentioned above.

    5. Follow the subsequent steps listed by Vikas.

    Let me know if you need samples or other info etc.

    Thanking you

    Mohamed Ibrahim

    Thursday, September 20, 2018 2:33 AM
  • Hi Mohamed Ibrahim,

    I can receive EDI file via HTTP in Logic Apps.

    I want to find a way insert multiple tables at once, like BizTalk SQL adapter.

    Is there a way to do it?

    Thanks in advance.

    Thursday, September 20, 2018 5:49 AM
  • Hi yuan012,

    I wrote an article on TechNet wiki on how to insert data into multiple tables in On Prem SQL server using triggers Logic Apps 101: Inserting Data Into Multiple Tables Using SQL Connector and Trigger(SQL) see if that helps you.


    Mandar Dharmadhikari


    Thursday, September 20, 2018 6:09 AM
  • Hi,

    Another option is using Azure Function with Linq to SQL. In this scenario, you can work with DataContext and retrieve your tables as objects. If you donot prefer Stored procedures, this approach can make you experience the implementation a bit similar to BizTalk SQL adapter.

    https://stackoverflow.com/questions/40066219/how-to-setup-the-definition-file-to-use-linq-in-an-azure-function

    Thanking you,

    Mohamed Ibrahim


    Thursday, September 20, 2018 6:55 AM
  • Hi Mohamed Ibrahim,

    I will try Azure function.
    It would be great if there could have an action like on-premises BizTalk SQL Adapter in Logic Apps.

    Thanks in advance.

    Thursday, September 20, 2018 7:27 AM
  • Hi,

    Yes, some kind of WSDL or schema generation tool might help here. If you have access to BizTalk Wizard tools in your dev environment, you can generate the schemas of the xml object, then call a stored procedure.

    If my answers help, please mark them as answers.

    Thanking you,

    Mohamed Ibrahim

    Thursday, September 20, 2018 8:04 AM
  • Hi,

    You can Hit Biz Talk Server from logic Apps.

    

    But First you need configure the On premise gateway. Then if you are using BizTalk Server 2016. you can configure Logic App Adapter Like below.

    https://docs.microsoft.com/en-us/biztalk/core/logic-app-adapter

    Then you can Exchange Messages.

    you can also Drop the messages into Service Bus from lgic Apps. from there, BizTalk will use SB Messaging adapter and Take the Messages and Insert into SQL Tables


    Sujith

    Thursday, September 20, 2018 12:01 PM
  • Hi Sujith,

    Because my customer thinks that on-premises BizTalk too expensive.

    So I want to try and see if Logic Apps can do the same thing as on-premises BizTalk.Thanks in advance.

    Friday, September 21, 2018 1:31 AM
  • Hi yuan,

    you can do it with Logic Apps as well.

    if you are using on premise SQL Server then you install on premise Gateway so that your Logic Apps can Connect.

    then you can use the Execute Stored Procedure Action if you have the Stored Proc written on your SQL to insert into multiple Tables.

    if you don't have you Probably have to use Insert Rows Actions which will insert into one table at a time. so you have to do it 2 times.



    Sujith

    Friday, September 21, 2018 1:04 PM