Using the HTTP Connection Manager

Web Service comes with an SSIS connection manager component that can be used to establish HTTP connections.

To add a connection to your SSIS package, right-click the Connection Manager area in your Visual Studio project, and choose "New Connection..." from the context menu. You will be prompted the "Add SSIS Connection Manager" window. Select the "HTTP Connection Manager  (KingswaySoft)"  item to add the new connection manager.

new connection

Add SSIS HTTP Connection Manager

The HTTP Connection Manager contains the following three pages to connect to the HTTP server.

  • General
  • Advanced Settings

General page

The General page on the HTTP Connection Manager allows you to specify general settings for the connection

SSIS HTTP Connection Manager

Base URL

The URL field represents the address of the web service to connect to.

Timeout (secs)
The Timeout (secs) option allows you to specify a timeout value in seconds for the connection. The default value is 120 seconds.
Chunk Size (in KB)
The chunk size to use when sending web requests.  If a size of 0 is used chunking will not be used.
API Throttling Rate
The API Throttling Rate will limit the number of requests that can be sent per second.  This is useful when accessing APIs with request limits.
Custom Headers
The custom headers grid will allow http headers to be entered that will be used for each request.

Security page

The security page allows you to specify the authentication mode and certificate that will be used with the connection.

Use Client Certificate

The use client certificate checkbox will determine if a client certificate will be used when accessing the web service.  Checking this box will enable the "Get Certificate From Store" and "Get Certificate From Filesystem" buttons which will allow a certificate to be chosen.

There are three authentication modes: Credentials, OAuth and None

Credentials Mode

SSIS HTTP Connection Manager - Security Credentials

Domain 

The Domain   to use when accessing the web service.

Username 

The Username to use when accessing the web service.

Password

The Password to use when accessing the web service.

OAuth Mode

SSIS HTTP Connection Manager - Security Oauth

Path to Token File:

The path to the token file on the file system.

Token File Password:

The password to the token file.

Put Access Token In

The part of the web request where the token should go, either Header or Query String

Advanced Settings page

The Advanced Settings page on the HTTP Connection Manager allows you to specify some advanced and optional settings for the connection.

SSIS HTTP Connection Manager - Advanced Settings

Proxy Mode (since v3.0)

Proxy Mode option allows to specify how you want to configure the proxy server setting. There are three options available.

  • No Proxy
  • Auto-detect (Using system configured proxy)
  • Manual
Proxy Server

Using Proxy Server option allows you to specify the name of the proxy server for the connection.

Port

The Port option allows you to specify the port number of the proxy server for the connection.

Username (Proxy Server Authentication)

Username option (under Proxy Server Authentication) allows you to specify the proxy user account.

Password (Proxy Server Authentication)

Password option (under Proxy Server Authentication) allows you to specify the proxy user's password.

NOTE:  The Proxy Password is not included in the connection manager's ConnectionString property by default. This is by design for security reasons. However, you can include it in your ConnectionString if you want to parameterize your connection manager. The format would be ProxyPassword=myProxyPassword;  (make sure you have a semicolon as the last character). It can be anywhere in the ConnectionString.

Retry on Intermittent Errors

The retry on intermittent errors determines if requests will be retried when there is an error.  If this option is checked requests will be retried up to 3 times.

Using the OAuth 2 Token Generator

The OAuth 2 Token Generator is used to generate token files for the HTTP Connection Manager. The HTTP Connection Manager will then use the file to refresh tokens as needed. Before getting started you will need to have a developer account for the service you wish to connect to. After that you will need to create an 'App' in the developer console. Search the API/Documentation of the service to find out how to do this.

General Settings Page

This page contains basic sign in information for the service you wish to connect to. The information can be found in the developer console in your apps settings.

OAuth 2 Token Generator - General Settings

Sign In URL 

This is the base url that you will be directed to when authentication is required.

Client Id
The client_id OAuth parameter.

Client Secret
The client_secret OAuth parameter.
Scope
The scope OAuth parameter.

Redirect URI
The redirect_uri OAuth parameter.
Scope
The allowed endpoints for your app.

Authorize App Page

This page allows you to sign into an account and authorize your app.

OAuth 2 Token Generator - Authorize App

Edit URL...  

Launches an editor that allows you to edit the sign in URL. This is for if the OAuth workflow requires additional parameters.

Authorize In App...
Launches a web browser within the OAuth 2 Token Generator that allows you to sign in and authorize your app. The generator will automatically get the code parameter from the redirect URI.

Authorize In Browser...
Launches the default web browser and opens a dialog containing a textbox. Complete the sign in and authorization process in the browser and when redirected to the redirec_uri, copy the entire URL and paste it in the textbox.

Request Tokens Page

This page will use the code retrieved from the Authorize App page and request tokens from the service.

OAuth 2 Token Generator - Request Tokens

Request Tokens URL 

The base URL to request tokens from.

Edit Body...
Launches an editor that allows you to edit the request body. This is for if the OAuth workflow requires additional parameters.

Get Tokens
Makes a request using the Request Token Url and Request Body to request tokens from the service.

Refresh Tokens Page

This page is the same as the Request Tokens Page. Make any necessary changes to the URL and Body then click the Refresh Tokens button to continue.

OAuth 2 Token Generator - Refresh Tokens

Save Tokens Page

This page is used to save tokens and the settings to an encrypted file.

OAuth 2 Token Generator - Save Tokens

Token File Password

The password used to encrypt the file. You will need to provide this to the HTTP Connection Manager.

Save Tokens...
Launches a save file dialog. You will need to provide the path to this file to the HTTP Connection Manager.