none
BizTalk sql design question RRS feed

  • Question

  • we have total 150 sp's to get the data from sql. currently running these sp's and generating reports is a manual task. in project they are planning to use Biztalk to run these sp's and generate reports say in csv format. 

    so what will be the best design approach using biztalk for this?

    Regards,

    Amit


    Regards, Amit More

    Tuesday, April 30, 2013 8:20 AM

Answers

  • IMHO this requirement should be converted into a BI DWH implementation where the extraction (sp's) can be scheduled using another job and once the DWH cubes are prepared, the reporting happens.

    Regards.

    • Proposed as answer by Chandra Kumar Tuesday, April 30, 2013 2:29 PM
    • Marked as answer by Amit C More Thursday, May 2, 2013 2:08 AM
    Tuesday, April 30, 2013 9:04 AM
  • Hi Amit,

    Shankycheil refers DWH as Data Warehouse.

    I would also go for Shankycheil approach for the better design for your requirement. Considering the number of Sps involved (which I assume could be due to the complexness/ disparateness of the data) I would keep the report generation process with SQL. And datawarehousing is meant for these types of requirements.

    If you still need to use BizTalk for some reason, then as Mohit suggested, BizTalk comes with SQL adapter (WCF-SQL) which you can use to integrate with SQL and convert the output to CSV format as you want.

    Regards,

    M.R.ASHWINPRABHU


    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful.

    Tuesday, April 30, 2013 9:21 AM

All replies

  • Hi

    you can use WCF - SQL Adapter , Typed Polling to retrieve data from SP's.

    Convert into Csv Format and send file .

    Regards

    MG

    Tuesday, April 30, 2013 8:35 AM
  • ok, so in that case do i need to create 150 receive location? because managing 150 receive location will be the tough task.

    what i was planning is to have one web service published, client consume the web service, from web service he will send request to BizTalk for type of sp he wants to run, biztalk will execute the sp, get the result and send that to caller.

    but i am not sure how i will achieve this, can we call sp dynamically?

    Regards,

    Amit


    Regards, Amit More

    Tuesday, April 30, 2013 9:02 AM
  • IMHO this requirement should be converted into a BI DWH implementation where the extraction (sp's) can be scheduled using another job and once the DWH cubes are prepared, the reporting happens.

    Regards.

    • Proposed as answer by Chandra Kumar Tuesday, April 30, 2013 2:29 PM
    • Marked as answer by Amit C More Thursday, May 2, 2013 2:08 AM
    Tuesday, April 30, 2013 9:04 AM
  • hi Shankycheil, 

    I am not sure what BI DWH stands for, these sp's are run by user as per their time, its not a scheduled task/job. right now they are running this manually and getting the result back. and will like to reduce this manual task and also direct database access.

    Regards,

    Amit


    Regards, Amit More

    Tuesday, April 30, 2013 9:13 AM
  • Hi Amit,

    Shankycheil refers DWH as Data Warehouse.

    I would also go for Shankycheil approach for the better design for your requirement. Considering the number of Sps involved (which I assume could be due to the complexness/ disparateness of the data) I would keep the report generation process with SQL. And datawarehousing is meant for these types of requirements.

    If you still need to use BizTalk for some reason, then as Mohit suggested, BizTalk comes with SQL adapter (WCF-SQL) which you can use to integrate with SQL and convert the output to CSV format as you want.

    Regards,

    M.R.ASHWINPRABHU


    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful.

    Tuesday, April 30, 2013 9:21 AM
  • Hi Amit,

    I would recommend using SQL applications and not BizTalk to do this as mentioned by previous replies or you can use SSIS to do the job for you.


    Regards, Mazin - MCTS BizTalk Server 2006

    Tuesday, April 30, 2013 10:56 AM
  • Thanks Ashwinprabhu...


    Regards, Amit More

    Thursday, May 2, 2013 2:07 AM