Share via

SSIS REST API Authentication

mmarsbarr 21 Reputation points
2021-06-11T11:04:27.08+00:00

Hello.

I would like to use SSIS to make a REST API call to retrieve data.

I have done a simple version of this before where I use an Execute Process Task to run a curl command that passes the authentication token and URL Endpoint in the one line, the Script Task then takes the JSON response and pipes it to a file, simple.

104738-image.png

With a new REST API service I'm working with (https://developer.aventri.com/#data-services) , a call first needs to be made to authenticate;
I pass the two constants accountid=123456789 and key=ABCD1234

https://api-apac.eventscloud.com/api/ds/v1/authenticate?accountid=123456789&key=ABCD1234

The result of issuing the above http then gives a token;

{"status":"success","accesstoken":"XYZ1234","msg":""}

I then use this token XYZ1234 to issue the http request to grab some data with a JSON response along with the accountid (123456789) constant used earlier;

https://api-apac.eventscloud.com/api/ds/v1/eventlist/123456789&accesstoken=XYZ1234

With a beginner level experience of SSIS, I wanted to know how to achieve this two-step process?

I would imagine I'd need to create two package variables which are constants for accountid and key. I then would probably need to create another empty variable for the return token eg. sessiontoken

1) What kind of SSIS Toolbox Task would I need to use to issue the initial HTTP authentication?
2) How can I capture the response and assign the value from "accesstoken":"XYZ1234" to the package variable sessiontoken so variable sessiontoken = XYZ1234?
3) How do I issue another HTTP task with the variables to then get the JSON response?

Would appreciate your help, thanks.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,523 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 43,166 Reputation points
    2021-06-11T11:23:18.547+00:00

    You can use a SSIS .NET script task to call the API and process the response, but that requires .NET programming knowledge.
    Example see
    https://www.c-sharpcorner.com/article/how-to-consume-web-api-through-ssis-package/

    1 person found this answer helpful.
  2. CarrinWu-MSFT 6,866 Reputation points
    2021-06-14T07:48:51.257+00:00

    Hi @mmarsbarr ,

    Welcome to Microsoft Q&A!

    Sorry I am not familiar with C#. But I do some research, hope the following links will be helpful:
    SSIS Web Service Tasks
    Perform HTTP get operation in SSIS to fetch information from an API

    Best regards,
    Carrin

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.