Calling Web API in SSIS with Authorization and Redirect

Introduction

In some cases when you call API to http URL it may redirect you to different location (Also known as 301 or 302 redirect). Best example is when you type https://www.zappysys.com it will redirect you to https://zappysys.com. This can cause issue in some case if you are passing credentials (i.e. UserID / Password) because by default your credentials are not passed to redirected URL (This is security feature to avoid sending your credentials to different URL than you anticipated). So how to send Authorization to second URL if second URL expects same credentials too? Well not to worry… it’s just one extra step in that case. Lets see how to do that.

Pass Authorization to 301 / 302 Redirect URL

Assume that you have to call below API URL which requires Basic Authentication (UserID/Password) and it also redirects to some other URL which also needs same credentials. For this purpose we need to mainly perform below important changes.

1. First drag ZS REST API Task from SSIS Control Flow Toolbox
2. Change Url mode to URL from connection. Click New Connection.
3. Configure your connection with API Url, UrserID and Password as below. Click OK to save connection.
   

   SSIS REST API Example – Call with 301 / 302 Redirect

4. Now go to Advanced Tab and Check Prevent 301 redirect option
   

   SSIS REST API Task – Disable automatic redirect option

5. Now go to Response Settings Tab and Map Location Header as below (Assuming you have created SSIS Variable named NewLocation (string type) )
   

   SSIS REST API Task – Map response header to SSIS Variable

6. Click OK to save.

Call API URL from URL

1. Now drag another ZS REST API Task from SSIS Control Flow Toolbox
2. Change Url mode to URL from connection. Select connection created in previous section.
3. Now configure your Url as below so we read it from Variable.
   

   SSIS REST API Task – Calling API URL from SSIS Variable

4. That’s it Save the task and run package.

Debug Web API call using Fiddler

To test how things looking behind the scene we strongly suggest to use tool like  fiddler.

See how Fiddler shows 302 Status code for First API call and see how it passes Authorization (UserID/Password). You can also test Just first task without [Prevent 301/302 redirect] option see what happens]

Debugging Web API call using Fiddler in SSIS