locked
Calling Rest API to load data into SQLServer tables via SSIS RRS feed

  • Question

  • Hi All,

    I wanted to call REST API to load data into SQL server. My knowledge with API  is very minimal(including c# and python) and done some basic research and it is suggested it can be done via various approach. I would like to know which is the best way to go for it.

    Basically calling one API inside another.

    1) Call via SSIS package script component (c# code)

    2)Via Python script and execute the python script with SSIS package

    Many thanks for the help.

    Wednesday, August 15, 2018 8:34 AM

All replies

  • Another method is using CURL

    see

    https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/04/17/calling-http-endpoints-in-t-sql-using-curl-extension/


    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

    Wednesday, August 15, 2018 8:41 AM
  • Hi SSQLL,

    Yes, there are various ways to get data from Rest/Restful API in SSIS, as you know, there's no built-in component, basically you need to achieve this programmatically or use third-party component.

    To simplify development, you can use third-party component, basically these components have full features for Rest/Restful API and easy to design, e.g. Call REST API using SSIS Web Service Task / JSON / XML Source. Also, you may need to purchase them if you would like to using them in production environment.

    Besides, personally, I would suggest you are using Built-in Script Component as Source to fetch data from Rest/Restful API as it's free and easy to debug and maintenance. In this way, you may need to install some libraries and install them into GAC to reference them. A example for your reference:

    How To Consume Web API Through SSIS Package

    A Few Great Ways to Consume RESTful API in C#

    And you also mentioned that use other process to consume the Rest API and load them into SQL Server, it is practicable. However, it may be involved in more resources besides SSIS runtime engine, and it's hard to troubleshoot when issue occurs as the Execute Process Task usually just throw exit code. 

    Check if this helps.

    Regards,

    Pirlo Zhang 


    MSDN Community Support<br/> Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.


    • Edited by Pirlo Zhang Wednesday, August 15, 2018 9:33 AM
    Wednesday, August 15, 2018 9:31 AM
  • There are several third party tools to call REST API and they do not  require to use code, scripts or python.

    You only need to provide the URL and credentials. 

    For example, you can try the ZappySys REST API task in SSIS:https://zappysys.com/products/ssis-powerpack/ssis-rest-api-web-service-task/


    MVP MCT MCTS Daniel Calbimonte

    http://elpaladintecnologico.blogspot.com

    Friday, October 11, 2019 10:18 PM
  • You may also check the commercial COZYROC SSIS+ library that can be configured to connect and consume data from any REST API. For further information review this page.

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

    Monday, October 14, 2019 12:57 PM