none
SSIS Vs BizTalk

    Obecná diskuse

  • Hi Guys,

    I have a situation which I am facing in my organisation where BizTalk is being suggested instead of SSIS in below scenario.

    For the scenario mentioned below i think we should use SSIS as an ETL tool. Any advise would be really helpful.

    Scenario: Pull data from API (adhoc and overnight batch load). BizTalk team is suggesting to use BizTalk as they can easily write BizTalk services to pull data from API and they can communicate using a generic port which is less prone to cyber attacks.  Whereas if we use SSIS we will have to write more custom code and would require firewall changes to use a less secure port to communicate.

    Any suggestions greatly appreciated.



    Thanks, Pulkit Ojha Microsoft Business Intelligence Developer

    středa 16. května 2018 8:32

Všechny reakce

  • Is it only API call for data import you want to or are there other functionality also required like data cleansing, data transformation etc?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    středa 16. května 2018 10:16
  • Here are some topics and threads covering different between Biztalk and SSIS,

    https://social.msdn.microsoft.com/Forums/en-US/11b83c14-f78b-46ff-a0b0-2d33716010e4/biztalk-server-vs-ssis?forum=biztalkgeneral

    https://www.sqlservercentral.com/Forums/1487156/SSIS-vs-BizTalk

    http://geekswithblogs.net/wmichel/archive/2007/04/11/111470.aspx

    If the transformation isn't available in SSIS out of the box and willing to bear extra licensing cost for Biztalk , then it should be better to go with Biztalk. Biztalk allows to design robust orchestration and transformation.

    On part of security , unless API you are referring to is from outside Intranet then it would be risk .


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    středa 16. května 2018 11:13
  • Thanks Visakh.

    There will be some cleansing and transformation required after the output of the API call has been received.

    So basically our new source system supports API and we need to hit the API's to load the incremental data either more frequently (drip feed throughout the day) or as a batch (normally last day's transaction).

    Hope that makes it clear.


    Thanks, Pulkit Ojha Microsoft Business Intelligence Developer

    středa 16. května 2018 11:18
  • On security aspect, to make the data extraction solution future proof, the API's could be hosted in cloud. So yes I also need to consider if SSIS will be able to securely connect to an API in the cloud outside our company network.

    So Sarat, are you suggesting that if SSIS had to do what I have mentioned above will be a security issue??


    Thanks, Pulkit Ojha Microsoft Business Intelligence Developer

    středa 16. května 2018 11:20
  • On security aspect, to make the data extraction solution future proof, the API's could be hosted in cloud. So yes I also need to consider if SSIS will be able to securely connect to an API in the cloud outside our company network.

    So Sarat, are you suggesting that if SSIS had to do what I have mentioned above will be a security issue??


    Thanks, Pulkit Ojha Microsoft Business Intelligence Developer

    I am just saying SSIS will be of more weight if security isn't a concern to worry. But from what you said the data could be coming a cloud hosted service, so security might play major part in your decision.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    středa 16. května 2018 11:28
  • Thanks.

    Thanks, Pulkit Ojha Microsoft Business Intelligence Developer

    středa 16. května 2018 11:35
  • Thanks Visakh.

    There will be some cleansing and transformation required after the output of the API call has been received.

    So basically our new source system supports API and we need to hit the API's to load the incremental data either more frequently (drip feed throughout the day) or as a batch (normally last day's transaction).

    Hope that makes it clear.


    Thanks, Pulkit Ojha Microsoft Business Intelligence Developer

    Then you can do like this

    Use Biztalk for the API call and get incremental data onto a staging table

    Then have a SSIS package to use this staging table data to do cleansing and transformation and load it to your final table.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    středa 16. května 2018 11:48
  • Then are we saying that SSIS should not be a preferred choice to communicate with cloud API's ??

    Thanks, Pulkit Ojha Microsoft Business Intelligence Developer

    středa 16. května 2018 12:08
  • If you are pulling from an API, why would there be firewall changes and less secure port.  The port and firewall changes would the same in both circumstances.

    In any case, BizTalk and SSIS are not really comparable.  They have some overlap, but do not directly compete.  So it is hard to compare.

    If the API is returning an XML or JSON result, then BizTalk may be able to parse it easier in order to put it into a database table.  However, you said you need custom transformations, which is difficult to do in BizTalk, requiring custom coding.

    středa 16. května 2018 12:19
  • Hi Pulkit,

    Unless you need features like reliable messaging or event based processing, there is simply no reason to use BizTalk. In fact BizTalk can be extremely slow if you have to process large amounts of data.

    Also you can make communication with SSIS over standard HTTP/HTTPS protocols and ports. Therefore there is no difference in security compared to a BizTalk. 


    SSIS Tasks Components Scripts Services | http://wwww.cozyroc.com/

    čtvrtek 17. května 2018 2:00
  • Thanks for all the help. I think I need to do a quick POC with SSIS.

    Thanks, Pulkit Ojha Microsoft Business Intelligence Developer

    pátek 18. května 2018 8:47