none
How to connect to a REST API with userid/password using SSIS RRS feed

  • Question

  • Hello Everyone! Today I was given a task where we will have to consume an REST API and load data into a table for further massaging. I tried to google and see how this can be done. But didn't find much material.

    This API will be spitting out a JSON stream. And will need a userid/password to connect. I understand I will have to write a custom script to achieve this. 

    I will really appreciate any help!!

    Thank you,

    Abhi

    Wednesday, March 21, 2018 7:35 PM

All replies

  • Hi Abhi425,

    The easiest is to use 3rd party tools as those say from CozyRoc ( I am not affiliated with them).

    Otherwise yes, you need to be coding.

    It is not also a very typical task for SSIS to do so because typically it is behind firewalls in DMZ.

    Anyways, to the point, just borrow the example from here: https://docs.microsoft.com/en-us/aspnet/web-api/overview/advanced/calling-a-web-api-from-a-net-client

    And put into a Script Task. This is all.


    Arthur

    MyBlog


    Twitter

    Wednesday, March 21, 2018 8:31 PM
    Moderator
  • Thank you Arthur!!

    Our client is not in favor of buying 3rd party tools. I not great at c# but can give it a shot with some articles in hand.

    Abhi

    Wednesday, March 21, 2018 10:14 PM
  • Hi Abhi425,

    It depends on your rest api use which authentication. Assume it is HTTP Basic authentication, sample code looks like:

        WebRequest req = WebRequest.Create(@"https://sub.domain.com/api/operations?param=value&param2=value");
        req.Method = "GET";
        req.Headers["Authorization"] = "Basic " + Convert.ToBase64String(Encoding.Default.GetBytes("username:password"));
        //req.Credentials = new NetworkCredential("username", "password");
        HttpWebResponse resp = req.GetResponse() as HttpWebResponse;

    Below articles for your reference:

    Call a Web API From a .NET Client (C#)

    Using a JSON Feed as a Data Source in SSIS

    Regards,

    Pirlo Zhang


    MSDN Community Support
    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 MSDNFSF@microsoft.com.

    Thursday, March 22, 2018 2:21 AM
    Moderator
  • I used a 3rd party task named JSON Source from ZappySys

    It is required to use the ZS-HTTP Connection (the installer includes that connection)

    

    And then you can use that connection with basic authentication in the JSON Source:


    MVP MCT MCTS Daniel Calbimonte

    http://elpaladintecnologico.blogspot.com

    Sunday, March 10, 2019 3:51 AM