locked
Passing Values to a Web API using SSIS RRS feed

  • Question

  • Hi guys,

    I am designing an SSIS package that will query an SQL table and then passing values from the SQL Query result-set to a Web API.

    I actually plan to use Execute SQL get the SQL query result and and save it as an object variable and then use the Foreach ADO Enumerator to loop through each of the rows in the result set and then pass the values as a parameter to the Web API. 

    My major challenge is how to write a code that will handle the passing of the values to the API as I am not very good in writing codes.

    Thanks



    me

    Tuesday, January 15, 2019 10:06 PM

Answers

  • @Yang.Z.

    So , eventually I found out that the authorization method is not Basic but Bearer.

    So I tried below and it worked for me:

    webRequest.Headers["Authorization"] = "Bearer " + token

    me

    • Marked as answer by lilutchay Wednesday, January 23, 2019 7:02 PM
    Wednesday, January 23, 2019 7:02 PM
  • Hi lilutchay, 

    Then please check the script in this link: 

    Making a POST request in C# with Basic Authentication

    var webRequest = WebRequest.Create(uri);
    webRequest.Headers["Authorization"] = "Basic " + Convert.ToBase64String(Encoding.Default.GetBytes(username + ":" + password));
    webRequest.Method = "POST";
    var bytes = Encoding.UTF8.GetBytes(data);
    webRequest.ContentLength = bytes.Length;
    webRequest.ContentType = "application/x-www-form-urlencoded";
    using (var requestStream = webRequest.GetRequestStream())
    {
        requestStream.Write(bytes, 0, bytes.Length);
    }
    ...



    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

    • Marked as answer by lilutchay Wednesday, January 23, 2019 4:05 PM
    Wednesday, January 23, 2019 9:01 AM

All replies

  • Hi lilutchay, 

    Hope these links help:

    How to make HTTP POST web request

    How to: Send Data Using the WebRequest Class

    If you have any trouble, please post the error message, and provide more details about what you're trying to do. 


    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

    • Proposed as answer by Yang.Z Monday, January 21, 2019 6:40 AM
    Wednesday, January 16, 2019 10:36 AM
  • Okay. 

    Thanks so much Yang.Z. I will let you know how it went


    me

    Wednesday, January 16, 2019 3:24 PM
  • Hi Yang.Z, the GET method seems to be working fine but I am having some challenges with the POST.

    I think the issue I am having is because I need to pass the username and password while calling the api as well.


    me

    Tuesday, January 22, 2019 10:54 PM
  • Hi lilutchay, 

    Then please check the script in this link: 

    Making a POST request in C# with Basic Authentication

    var webRequest = WebRequest.Create(uri);
    webRequest.Headers["Authorization"] = "Basic " + Convert.ToBase64String(Encoding.Default.GetBytes(username + ":" + password));
    webRequest.Method = "POST";
    var bytes = Encoding.UTF8.GetBytes(data);
    webRequest.ContentLength = bytes.Length;
    webRequest.ContentType = "application/x-www-form-urlencoded";
    using (var requestStream = webRequest.GetRequestStream())
    {
        requestStream.Write(bytes, 0, bytes.Length);
    }
    ...



    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

    • Marked as answer by lilutchay Wednesday, January 23, 2019 4:05 PM
    Wednesday, January 23, 2019 9:01 AM
  • @Yang.Z.

    So , eventually I found out that the authorization method is not Basic but Bearer.

    So I tried below and it worked for me:

    webRequest.Headers["Authorization"] = "Bearer " + token

    me

    • Marked as answer by lilutchay Wednesday, January 23, 2019 7:02 PM
    Wednesday, January 23, 2019 7:02 PM