Help Manual - OData Connection Manager

Using the OData Connection Manager

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 "OData (KingswaySoft)" item to add the new connection manager.

The OData Connection Manager contains the following five pages to connect to the service.

• General
• Authentication
• OData Settings
• Proxy Settings
• Advanced Settings

General

The General page on the OData Connection Manager allows you to specify general settings for the component.

Server Information

Base URL

This option allows you to specify the URL of the OData service you are trying to connect to.

General Settings

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 set, chunking will not be used.

API Throttling Rate

The API Throttling Rate will limit the number of requests that can be sent per unit of time. This is useful when accessing APIs with request limits. The Requests drop-down will allow you to specify whether the API Throttling Rate should be per second, minute or hour.

Custom Headers

The Custom Headers grid will allow HTTP headers to be entered that will be used for each request. Clicking on the “+” button will add a row, and you can either choose a “Header name” from the drop-down list or specify one. Enter the “Value” in the respective field.

Test Connection button

The Test Connection button will allow you to test the connection manager for connectivity.

Authentication

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

Authentication Mode

There are nine authentication modes:

• AWS Signature
  • You would be able to specify the Signature Version, Access Key, Access Secret, Session Token, AWS Region, and Service name for AWS Signature authentication. Ths signature method could be chosen if Version 2 is selected as the Signature Version.
• Basic
  • In this mode, provide the basic Username and Password authentication.
• Credentials (Basic, Digest, NTLM)
  • Provide the Domain, Username, and Password for authentication.
• Custom Token
  • 'Custom Token' works by making a web request to retrieve an authorization token using a CURL command from a file. After the request is made the token is extracted from the response using one of the 'Find Token Strategies' and a 'Find Token Expression'. Once the token is extracted from the response it will be available for use by specifying '@Connection[AccessToken]' in supported connection properties or supported component properties. The token is re-retrieved after the specified expiry time in seconds.
• JWT
  • Provide JWT Claims and JWT Secret. The Import Claims from Existing JWT… button allows you to import from an existing JWT.
• Kerberos
  • In this mode, Specify Domain, Username, and Password for authentication.
• None
  • This mode can be chosen if no authentication is required.
• OAuth1
  • Generate an OAuth 1 Token using our OAuth Token generator. This can be opened by clicking on the Generate Token File… button.
• OAuth2
  • Generate an OAuth 2 token using our OAuth Token Generator, by clicking on the Generate Token File… button.

    

  • Path to Token File
    • The path to the token file on the file system.
  • Token File Password
    • The password to the token file that was generated.
  • Put Access Token In
    • The part of the web request where the token should go. There are three options available:
      • HTTP Header
      • Query String
      • Custom

OData Settings

The OData Settings page allows you to work with the OData service configurations.

Service Implementation

You could choose the Service Implementation you wish to work with. There are three options:

• CDS/Dynamics 365 Customer Engagement
  • Used for OData Service implementation that closely follows the Microsoft Web API specification.
  • Uses ‘@odata.nextLink’ when paging
• Generic
  • Used for OData service implementations that closely follow the standard OData specification.
• Generic - ‘Next Link’ Paging
  • Used for OData service implementations that closely follow the standard OData specification and use the ‘@odata.nextLink’ strategy when paging.
• SharePoint (since v21.1)
  • Used for OData Service implementation that closely follows the Microsoft SharePoint API specification.
  • Auto formats GUIDs in URLs.
  • Functions are not fully qualified.

Proxy Settings

The Proxy Settings page on the OData Connection Manager allows you to specify some optional settings for the connection.

Proxy Server Settings

Proxy Mode(since v3.0)

The Proxy Mode option allows you 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 the 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)

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

Password (Proxy Server Authentication)

The 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.

Advanced Settings

The Advanced Settings page allows you to specify advanced settings for the connection.

Client Certificate

Certificate Location

Allows you to specify the location of the certificate that will be used. There are two options available:

• Store
• File System

Certificate Thumbprint

Allows you to specify the thumbprint of the client certificate from the Certificate Store. Click the ellipses button to browse and select a certificate for the user's store. This option is available when you choose Store as Certificate Location.

Path to Certificate

Provide the path to the certificate file to use for authentication. Click the ellipses button to browse their file system for the certificate. This option is available when you choose File System as Certificate Location.

Certificate Password

Provide the password to the certificate file. This option is available when you choose File System as the Certificate Location.

Miscellaneous Settings

Ignore Certificate Errors

This option can be used to ignore those SSL certificate errors when connecting to the target server.

Warning: Enabling the "Ignore Certificate Errors" option is generally NOT recommended, particularly for production instances. Unless there is a strong reason to believe the connection is secure - such as the network communication is only happening in an internal infrastructure, this option should be unchecked for best security.

Note: When this option is enabled, it applies to all HTTP-based SSL connections in the same job process.

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 Token Generator

The OAuth2 Token Generator is used to generate token files for the OData Connection Manager. The OData 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

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 app's settings.

Import...

The Import button allows you to import OAuth 2 Token Generator settings from a settings JSON file (created before v10.0) or a token file (created after v10.0).

Grant Type

Specify the grant type for the OAuth2 authentication. Default options are Authorization Code, Client Credentials, and Password.

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.

Authorize App Page

This page allows you to sign in to an account and authorize your 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.

Request Tokens URL

The base URL to request tokens from.

Path to Access Token (since v6.0):

Specify the JPath to get the access token. The default path is "access_token".

Path to Refresh Token (since v6.0):

Specify the JPath to get the refresh token. The default path is "refresh_token".

Path to Expiry (since v6.0):

Specify the JPath to get the expiry. The default path is "expires_in".

Edit Body..

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

Edit Header.. (since v6.2)

Launches an editor that allows you to edit the request header. 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.

Access Token Lifespan:

The option would be available when the Expiry value is not received which can be used to specify the token expiration time manually.

Is Sliding Expiration:

Use the option to specify if the access token is sliding expiration.

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.

Save Tokens Page

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

Token File Password

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

Save Tokens...

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