none
Working example/script task on consuming data from REST API RRS feed

  • Question

  • Hi all

    I have googled my way through the internet searching for one working example of a script task in SSIS, that loads data into a table from an REST API. So far with no luck - it seems that everyone agree that it can be done using a script task, but it's apparently one of the best kept secrets.

    So please if someone knows and actually have done it themselves, please give me an example of how to make the following work.

    From the app company I got the following information

    "The partnerkey is used for authentication. The response returned from the service contains the SessionToken, use this token in subsequent calls to the service to authenticate yourself." 

    So I got the partnerkey. With the partner key I can get a sessionkey. Following info is provide from the app company

    Request (JSON)
    POST /login/application
    Content-Type: application/json
    {"Name":"micros", "Key":"<partnerkey goes here>"}

    And once that works, department can be retrieved with this

    Request (JSON)
    GET /departments/application/?securityToken=<token>
    Content-Type: application/json

    So how would this fit into a SSIS flow. First use partnerkey to get a sessionkey. Then use that sessionkey to get department data

    Thursday, November 24, 2016 11:39 AM

All replies

  • Hi,

    You need to perform a GET HTTP request.

    Put the code for the aforementioned shown in https://www.asp.net/web-api/overview/advanced/calling-a-web-api-from-a-net-client

    into a Script Task (a Script Transformation set to "Source" may work, too).

    The issues to consider is connectivity, the SSIS server must have access to the WWW which typically is not the case.

    Having JSON consumed you may need additional libraries, you just do not tell a word on the intended use.

    Also as a word of advise, consider using a commercial component say as CozyRoc's REST connection you can try for free in Development (disclaimer: I do not upsell their products).


    Arthur

    MyBlog


    Twitter

    Thursday, November 24, 2016 2:09 PM
    Moderator
  • The biggest point to remember is that when using a Script Task/Component, you aren't actually using SSIS but rather C# or VB with a hook in to SSIS to allow for passing back rows etc. So in such cases, look up what is available for c#/vb/.net
    Thursday, November 24, 2016 10:43 PM
  • Hi Jam2811,

    Here's a console application sample for your case, consisting of the code from internet. Please test and debug on your own and tweak it accordingly when applying it to script task or component. Just ignore my poor coding skill. :P

    using System;
    using System.IO;
    using System.Net;
    using System.Text;
    
    namespace ConsoleApplication11
    {
        class Program
        {
            static void Main(string[] args)
            {
    
                /*
                 Request (JSON)
                    POST /login/application
                    Content-Type: application/json
                    {"Name":"micros", "Key":"<partnerkey goes here>"}
                 */
    
                //reference http://stackoverflow.com/questions/9145667/how-to-post-json-to-the-server
    
                var httpWebRequest = (HttpWebRequest)WebRequest.Create(@"http://yoursite.com"+ @"/login/application");
                httpWebRequest.ContentType = "application/json";
                httpWebRequest.Method = "POST";
    
                using (var streamWriter = new StreamWriter(httpWebRequest.GetRequestStream()))
                {
                    string json = "{\"Name\":\"micros\", \"Key\":\"partnerkey goes here\"}";
    
                    streamWriter.Write(json);
                    streamWriter.Flush();
                    streamWriter.Close();
                }
    
                string tokenFromJson;
                var httpResponse = (HttpWebResponse)httpWebRequest.GetResponse();
                using (var streamReader = new StreamReader(httpResponse.GetResponseStream()))
                {
                    var result = streamReader.ReadToEnd();
    
                    /*
                    Supposing this request reponsee in JSON
                    Deserialize JSON with C#, according to your JSON structure
                    Check this sample link
                    http://stackoverflow.com/questions/7895105/deserialize-json-with-c-sharp
    
                    */
                    //supposing you've got the token from the JSON
                    tokenFromJson = "sometokenFromResult";
                }
    
                  
                /*
                 Request (JSON) 
                GET /departments/application/?securityToken=<token> 
                Content-Type: application/json
                 */
                 
                string requestUrl = @"http://yoursite.com"+ @"/departments/application/?securityToken="+ tokenFromJson;
    
                string finallyWhatYouGot = GET(requestUrl);
                Console.Write(finallyWhatYouGot);
                Console.ReadKey();
    
            }
    
             
            static string GET(string url)
            {
                HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
                try
                {
                    WebResponse response = request.GetResponse();
                    using (Stream responseStream = response.GetResponseStream())
                    {
                        StreamReader reader = new StreamReader(responseStream, Encoding.UTF8);
                        return reader.ReadToEnd();
                    }
                }
                catch (WebException ex)
                {
                    WebResponse errorResponse = ex.Response;
                    using (Stream responseStream = errorResponse.GetResponseStream())
                    {
                        StreamReader reader = new StreamReader(responseStream, Encoding.GetEncoding("utf-8"));
                        String errorText = reader.ReadToEnd();
                        // log errorText
                    }
                    throw;
                }
            }
        }
    }
    



    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.

    Friday, November 25, 2016 7:26 AM
    Moderator
  • Thanks Eric

    I'll test it out in the weekend

    Christian

    Friday, November 25, 2016 9:20 AM
  • Hi Christian,

    How is it going?
     

    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.

    Wednesday, November 30, 2016 8:30 AM
    Moderator
  • Hello Jam,

    take a look in the following example for getting data from a API and bypassing these values in columns.

    It may isn't the exact solution to your specific logic, but it would help understand the logic behind this.

    json-feed-as-data-source-in-ssis (tested)

    Hope I helped you in a way.

    Wednesday, November 30, 2016 10:33 AM
  • Hi Eric

    Sorry i'm still having trouble getting it to work in SSIS.

    Is it totally rare to use SSIS to get data from REST API ? For the ones i have talked to so far the approach seems to be - get the data dumped on an ftp instead... :-)

    I'll try with http://dennysjymbo.blogspot.nl/2013/05/using-json-feed-as-data-source-in-ssis.html but since it's 3,5 years old post I would have imagined that MS would have come up with a component for this instead


    • Edited by Jam2811 Thursday, December 1, 2016 7:57 PM
    Thursday, December 1, 2016 5:20 PM
  • Hi Jam2811,

    It is actually rare, not because of lacking of capacity, but because it requires quite some coding skill(C# normally). In fact it is a more coding requirement other than SSIS'. So before getting it work in SSIS, ensure that the C# code works to access the API and fetch data. For coding and debugging flexibility, you can play the code in a C# console application at first.

    I've worked with REST API in some other applications and found it is not that difficult if there exists completed documentation and test sample. The sample code in my previous reply is a common process to get data from REST API, for details, it depends on your Specific API and you shall tweak accordingly.
     
     

    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.

    Friday, December 2, 2016 1:40 AM
    Moderator
  • Thanks for the feedback.

    I have asked around and there is actually no one that I know in the SSIS/DW/BI community that I know who have ever worked with pulling data out of a REST API to date. The approach is always to find another way of getting the data out (flat file on FTP or connecting to the DB behind through ex. VPN/encrypted connection).

    There seems to be a bit of a gap between what web/application developer think of REST API and BI/DW developers think of REST API. Where the BI/DW Developer would normally ask for all data for the entire hour/day/month the web/application side thinks mainly in being able to server single transactions only...

    Friday, December 23, 2016 1:44 PM
  • Did you ever get this working? I am trying to achieve something similar using a Swagger wrapped Restfull API - bit of a challenge!!

    John

    Thursday, November 23, 2017 5:07 AM
  • I wanted to share what I did to make the RESTFull API call works in SSIS.

    - Create HTTP Connection (URL and credential)

    - Use Script Task Editor to call the HTTP Connection and Download file. My case is XML file.

    - Import the XML file to SQL server.

    - Create stored procedure to read the XML file using sp_xml_preparedocument

    - Call the store procedure in the SSIS Data Flow control.

    Hope this help. 

    Wednesday, January 24, 2018 3:50 PM
  • Could you please share your Script Task code to call HTTP Connection ?
    Wednesday, November 7, 2018 5:50 AM