locked
biztalk 2010 integration with multiple sql server RRS feed

  • Question

  • I have tables in sql server 1 (inbound) that needs to talk to tables in sql server 2 (outbound) via biztalk. (real time processing)

    My approach is create a store procedure in sql server 1 and call all these tables in SP, generate the tables using store procedure , using add generated items>consume adapter service>sql binding then schemas will be generated, from that point, I place sql server 1 schemas in my source in the biztalk mapping.

    Then create another store procedure in sql server 2 and call all these tables in SP, generate the tables using store procedure, using add generated items>consume adapter service>sql binding then schemas will be generated, from that point, I place sql server 2 schemas in my destination in the biztalk mapping.

    Then I map sql server 1 schemas as source to sql server 2 schemas as destination.

    at the end configure send ports using wcf sql adapter using the binding files that got generated with the schemas.

    Is my integration missing anything? especially from mapping stand point?

     

    Monday, March 16, 2015 3:37 PM

Answers

  • Don’t know why you need BizTalk here to do data transmission between two SQL servers. SSIS is meant for this types of ETL processes.

    Anyway if you still want to use BizTalk for some reason which we don’t know, then yes the steps which you have highlighted should be enough on high level.

    You need to map data structure from one SQL server to another which can be used using maps and consume adapter service shall able to generate the schema for the service/SQL.


    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.

    • Marked as answer by Chris7887 Monday, March 16, 2015 5:07 PM
    Monday, March 16, 2015 4:16 PM
  • Yes I totally agree with Ashwin here, normally if you do communication between the same technology/system, for example in your case source and destination both are SQL. So in this case BizTalk is not required, you should use SSIS.

    SSIS provide almost similar functionality and you can do basic mapping easily.

    In case you have to use BizTalk then your steps looks good for now, in case you are stuck just post a new question with the error message :)


    Thanks,
    Prashant
    ----------------------------------------
    Please mark this post accordingly if it answers your query or is helpful.

    • Marked as answer by Chris7887 Tuesday, March 17, 2015 3:19 PM
    Monday, March 16, 2015 5:12 PM

All replies

  • Don’t know why you need BizTalk here to do data transmission between two SQL servers. SSIS is meant for this types of ETL processes.

    Anyway if you still want to use BizTalk for some reason which we don’t know, then yes the steps which you have highlighted should be enough on high level.

    You need to map data structure from one SQL server to another which can be used using maps and consume adapter service shall able to generate the schema for the service/SQL.


    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.

    • Marked as answer by Chris7887 Monday, March 16, 2015 5:07 PM
    Monday, March 16, 2015 4:16 PM
  • Yes I totally agree with Ashwin here, normally if you do communication between the same technology/system, for example in your case source and destination both are SQL. So in this case BizTalk is not required, you should use SSIS.

    SSIS provide almost similar functionality and you can do basic mapping easily.

    In case you have to use BizTalk then your steps looks good for now, in case you are stuck just post a new question with the error message :)


    Thanks,
    Prashant
    ----------------------------------------
    Please mark this post accordingly if it answers your query or is helpful.

    • Marked as answer by Chris7887 Tuesday, March 17, 2015 3:19 PM
    Monday, March 16, 2015 5:12 PM
  • Thanks, I will keep you posted :)
    Tuesday, March 17, 2015 3:19 PM