OAuth Connection Manager
 PreviousNext

OAuth Connection Manager can be used along with REST API Task, JSON Source and XML Source to communicate with REST API Services which supports OAuth Security Protocol (e.g. Twitter, Facebook, Linkedin, Google, Microsoft). This connection manager handles complex authorization flow to obtain initial Token and renewing token automatically once its expired.

Download SSIS PowerPack

Content

Video Tutorial

Coming soon...

Step-By-Step

In this tutorial you will learn how to create OAuth Connection. You can create multiple connection(In this case its Linkedin and SalesForce).
  1. Firstly, You need to obtained suitable credentials for OAuth connection. Now we will create LinkedIn Application from here.
  2. Now, You need to Download and Install SSIS ZappySys PowerPack.
  3. Once you finished above steps, Open Visual Studio and Create New SSIS Package Project.
  4. Right click in the Connection Manager panel and click on New Connection.
    SSIS Create New Connection
  5. Select ZS-OAUTH Connection Manager from the Connection Managers list and Click on Add Button.
    SSIS OAuth Connection
  6. Configure new OAuth Connection for LinkedIn as below. Basically, you need to enter 3 pieces on the General tab (i.e. ClientId, ClientSecret, Scopes) and one more info on the Advanced tab (i.e. callback URL ). If you are not sure what is Scope then check this linkedin API documentation. For each API page you will see which permission needed. Just include the desired scope on ZappySys OAuth Connection (see below).
    SSIS OAuth Connection - Configure
  7. Set callback URL into Advanced Tab.
    SSIS OAuth Connection - Configure - Advanced Tab
  8. Click on Test Connection to check configure correct or not.
  9. Click on OK button to save OAuth Connection Manager configure setting UI.
  10. Once you finished, you will see OAuth connection in Connection Manager panel.
    SSIS OAuth Connection - Success

How to create SalesForce OAuth Connection using Custom OAuth Provider.

  1. Firstly, You need to obtained suitable credentials for OAuth connection. Now To develop ETLs in Salesforce using SSIS you’ll need a Salesforce Developer account. If you don’t have one, don’t worry, you can create a limited version FREE developer account. Click here to Sign up for FREE developer account.
  2. Once you finished above step, Now create SalesForce OAuth App click here.
  3. Right click in the Connection Manager panel and click on New Connection.
    SSIS Create New Connection
  4. Select ZS-OAUTH Connection Manager from the Connection Managers list and Click on Add Button.
    SSIS OAuth Connection
  5. Configure new OAuth Connection for SalesForce as below. Set OAuth Provider to Custom, enter following credentials and one more info on the Advanced tab (i.e. callback URL ).

    Authorization URL 
    https://login.salesforce.com/services/oauth2/authorize
    Authorization Token URL 
    https://login.salesforce.com/services/oauth2/token
    Scope 
    full
offline_access
    SSIS OAuth Connection - Configure- Custom
  6. Set callback URL into Advanced Tab. 
    https://login.salesforce.com/services/oauth2/success
    SSIS OAuth Connection - Configure - Advanced Tab
  7. Click on Test Connection to check configure correct or not.
  8. Click on OK button to save OAuth Connection Manager configure setting UI.
  9. Once you finished, you will see OAuth connection in Connection Manager panel.
    SSIS OAuth Connection - Success

Click on following URL for create more OAuth connection.
  1. Click here for more information.
  2. Click here to create Google AdWord OAuth.
  3. Click here to create Google DLF OAuth.
  4. Click here to create Gmail OAuth.
  5. Click here to create Office 365 OAuth.
  6. Click here to create Facebook OAuth.
  7. Click here to create QuickBooks Online OAuth.
  8. You can search for more OAuth Connection in our blog click here.

Properties

Property Name Description
RetainSameConnection
UserName End-user’s username (only used when OAuth GrantType is Password).
CustomAuthHeader Custom header name which includes access token for each request. This option is useful for some API like eBay where you can use OAuth tokens for calling other API (e.g. SOAP / XML)
CustomTokenPrefix Prefix string you like to include before access token (e.g. Bearer). We will not add space after this prefix so use space after word if needed to separate prefix from token.
Provider

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Custom [0] Custom
Google [1] Google API (Generic - Other than below)
GoogleAnalytics [2] Google Analytics
GoogleSheets [3] Google Sheets + Drive
GoogleBigQuery [4] Google BigQuery
GoogleAdWords [5] Google AdWords
GoogleDrive [6] Google Drive
Facebook [7] Facebook
Twitter [8] Twitter
LinkedIn [9] LinkedIn
Dropbox [10] Dropbox
Yahoo [11] Yahoo
Amazon [12] Amazon
AmazonMWS [13] Amazon Marketplace Web Service (MWS)
AmazonAWS [14] Amazon AWS API v4 (Amazon Web Services)
EbaySandbox [15] eBay - Sandbox API
EbayProduction [16] eBay - Production API
Tumblr [17] Tumblr
MicrosoftLive [18] MicrosoftLive
Assembla [19] Assembla
CodeProject [20] CodeProject
Foursquare [21] Foursquare
GitHub [22] GitHub
Instagram [23] Instagram
MailRu [24] MailRu
SoundCloud [25] SoundCloud
SourceForge [26] SourceForge
Vkontakte [27] Vkontakte
Yandex [28] Yandex
MailChimp [29] MailChimp
HubSpot [30] HubSpot
XeroPrivateApp [31] Xero (Private App)
Vsts [32] VSTS (Visual Studio Team Services - TFS Online
OAuthVersion

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Default [0] Default
OAuth2 [1] OAuth2
OAuth1 [2] OAuth1
RenewBeforeSec How many seconds before token should be renewed. Enter 0 for default value=300 seconds (i.e. renew before 300 seconds). This setting is useful if API doesnt like token call too early (i.e. default 5 mins before expiration). If API token expiration is less than or equal to 600 seconds then renew before is set to 20 seconds before expiration.
RefreshTokenFilePath File path to store token refresh information. Specify this path only if you know that your API keeps changing refresh token when you fetch new access token. For most API you dont have to worry about this option.
EnableOAuthOverUrl Set this option to true if your API requires OAuth credentials and signature to be passed via URL QueryString rather than HTTP Header (Authorization Header). This option is only valid for OAuthVersion=1.0
DoNotIncludeEmptyParamsInSignature Do not include key=value pair in OAuth1 signature if value is empty. This setting is ignored for OAuth2. For certain API providers you have to check this setting to avoid signature mismatch.
SignatureMethod Signature Method for OAuth 1.0. Possible values are PLAINTEXT, HMAC-SHA1, HMAC-SHA256, HMAC-SHA512, RSA-SHA1, RSA-SHA256, RSA-SHA512
DoNotIncludeCredentialsInHeader When you set this option to true Authorization header is not sent when grant type=Password or client credentials (OAuth 2.0). Change this option only when server rejects credentials passed via header.
DoNotIncludeCredentialsInBody When you set this option to true then password and userid is sent via Authorization header (Base64 encoded) rather than Body (e.g. userid=myuser&password=mypass123). Change this option only when you get error if credentials found in body.
ContentType Set content type here (e.g. application/json) for /token call which needs JSON based input for POST call.
AccessTokenAttrName Set this to use non standard attribute name from the response for AccessToken (i.e. id_token). Leave this value blank to use default name as per OAuth 2.0 standard (i.e. access_token).
AuthScheme Auth scheme is a prefix in the Authorization header value. e.g. Authorization: Bearer xxxxxxxxxx. Default prefix is Bearer if you dont specify this property. To use blank prefix set this to {none} (including curly brackets). If your prefix has space then another space is not appended otherwise space automatically appended after prefix.
ScopeSeparator
ExtraAttributesForTokenRequest Some OAuth providers (e.g. Microsoft Dynamics AX API) may require you to pass additional information along with standard attributes (e.g. resource=SOME-URL) when you fetch new Access Token using Token URL. Enter additional attributes in key/value pairs separated by ampersand [&]  (e.g. Prop1=aaaa&Prop2=bbbb )
ExtraAttributesForAuthRequest Some OAuth providers (e.g. Zoho) may require you to pass additional information along with standard attributes (e.g. access_type=offline) when you start 3-legged oauth process using /auth URL. Enter additional attributes in key/value pairs separated by ampersand [&]  (e.g. Prop1=aaaa&Prop2=bbbb )
ReturnUrl OAuth Return URL. After initial login short lived authorization code is sent to this URL before AccessToken is acquired. If you don't have a secure URL then use https://zappysys.com/oauth/
OAuthGrantType OAuth grant_type to use. Default is AuthorizationCode. For 3-legged authorization its AuthorizationCode (Popup login form and obtains token). If you want to user UserId/Password to get token then use Password grant (No login form displayed). If you want to use ClientID/ClientSecret to get token then use ClientCredentials grant type (No login form displayed). Refer your API documentation before assuming that your API supports these three grant types. Its possible that your API may support only one grant type out of these three types (i.e AuthorizationCode, Password and ClientCredentials).

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Default [0] Default
AuthorizationCode [1] Authorization Code Grant (i.e. 3-Legged Auth)
Password [2] Password Grant
ClientCredentials [3] Client Credentials Grant
TokenRefreshMode

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Default [0] Default
AutoRefresh [1] AutoRefresh
NoRefresh [2] NoRefresh
RequestTokenUrl URL to get Access Token to make API calls.
SslVersion Ssl/Tls Protocol version for https URL. Change this setting if your server requires certain version of Ssl/Tls protocol.

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Default [0] System Default
Ssl3 [1] SSL v3.0
Ssl3Plus [2] SSL v3.0 or higher
Tls [3] TLS v1.0
TlsPlus [4] TLS v1.0 or higher
Tls11 [5] TLS v1.1
Tls11Plus [6] TLS v1.1 or higher
Tls12 [7] TLS v1.2
Tls12Plus [8] TLS v1.2 or higher
Tls13 [9] TLS v1.3
Any [65535] Any Version
ConnectionString
SupportPassword
UseClientCertificate UseClientCertificate
ClientCertificateStorageType CertificateStorageType

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
NotSet [0] <Not set>
LocalMachine [1] Stored in LocalMachine Storage Area
CurrentUser [2] Stored in User Storage Area
PfxFile [3] Disk File - PFX (PKCS12) format
ClientCertificateStoreName CertificateStoreName

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
NotSet [0] <Not set>
AddressBook [1] AddressBook
AuthRoot [2] AuthRoot
CertificateAuthority [3] CertificateAuthority
Disallowed [4] Disallowed
My [5] My
Root [6] Root
TrustedPeople [7] TrustedPeople
TrustedPublisher [8] TrustedPublisher
ClientCertificateThumbPrint CertificateThumbprint
ClientCertificatePath CertificatePath
Password End-user’s password (only used when OAuth GrantType is Password).
ClientCertificatePassword Password for X509 Client certificate
AccessTokenSecret Token secret used to sign OAuth 1.0 Request (OAuth 1.0 enabled services such as Twitter use this)
AuthorizationUrl OAuth Endpoint Url which should be used to start Authorization process (consent/login screen)
AccessTokenUrl OAuth Endpoint Url which should be used to request Access Token
Scopes Scope(s) for OAuth. This is only used when you authorize for first time (login screen).
RefreshToken Some OAuth 2.0 services such as Google API supports RefreshToken so when AccessToken expires you can request new AccessToken without going through manual authorization screen (login screen) again.
AccessToken AccessToken for authenticated REST API calls. This token is included along with each secure call you make to OAuth enabled REST API (Usually via Authorization Header. e.g. Authorization: Bearer xxxxxxxxxxxx). Some OAuth services such as Google expires AccessToken in short time (e.g. 1 hour) and some services such as Facebook has long lasting token (e.g. 60 days). Read your OAuth provider API documentation to learn more. If API supports renewing token then this connection manager will do automatically for you.
UseCustomApp UseCustomApp
ClientId Client Id or sometimes known as OAuth Application Id for your registered OAuth Application. If you are using DefaultApp provided by ZappySys then ignore this property.
ClientSecret Client Secret or sometimes known as OAuth Application Secret for your registered OAuth Application. If you are using DefaultApp provided by ZappySys then ignore this property.
RetryMode

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
None [0] Do not retry
RetryAny [1] Retry any web errors
RetryWhenStatusCodeMatch [2] Retry for matching response status code(s)
RetryWhenMessageMatch [3] Retry errors for matching error sub string(s)
RetryCountMax
RetryMessageList
RetryMultiplyWaitTime
RetryStatusCodeList
RetryWaitTimeMs
UseProxy Enable custom proxy settings (If this is not set then system default proxy will be used. To disable proxy totally uncheck this option and check DoNotUseDefaultProxy option if available)
ProxyUrl Web URL of Proxy server (including port  if necessary). [e.g. http://myproxyserver:8080/]
DoNotUseDefaultProxy Do not use system default proxy (This setting is ignored if UseProxy=true)
ProxyUseCreds Enable passing userid and password to proxy server
ProxyPassword Proxy password
ProxyUserName Proxy username

Setting UI

SSIS OAuth Connection - Setting UI

See Also

References

Articles / Tutorials

Click here to see all articles for [OAuth] category
Load data from Exact Online into SQL Server using SSIS

Load data from Exact Online into SQL Server using SSIS

Introduction In this article, you will learn how to load data from Exact Online into SQL Server using SSIS. Exact Online is a popular CRM, ERP and HRM tool which also lets you manage your finances. As an example, we will use Exact Online contacts as data to load it into a SQL Server database. […]

Read Visual Studio Team Services data in SSIS (TFS Online) – Call REST API / Load to SQL Server

Read Visual Studio Team Services data in SSIS (TFS Online) – Call REST API / Load to SQL Server

Introduction In this blog, we will learn How to read Visual Studio Team Services data in SSIS and load into SQL Server Table, along with few other topics such as. How to register an OAuth App for Visual studio Team Service – TFS Online (Team Foundation Server) REST API Call How to read all Issues data […]

How to Import / Export data from Zuora API using SSIS

How to Import / Export data from Zuora API using SSIS

Introduction In this post, we will learn how to call Zuora API using SSIS.  We will go through steps to read data from Zuora and load into SQL Server using  SSIS REST API Task  and  SSIS JSON Source Connector . At the end of this article, you will find a complete sample (dtsx file).   Tasks/Components in […]

Import / Export data from Amazon Athena using SSIS

Import / Export data from Amazon Athena using SSIS

Introduction In our previous post we explored unlimited possibilities to call Amazon AWS API using SSIS. Now in this post we will learn how to import / export data from Amazon Athena using SSIS. Amazon Athena is very similar service like Google BigQuery which we documented already.  To read data from Amazon Athena we will use […]

How to run SSIS package with sensitive data on SQL Server

How to run SSIS package with sensitive data on SQL Server

Introduction In this article we will cover the steps how to run an SSIS package with sensitive data on SQL Server. In SSIS package sensitive data such as tokens and passwords are by default encrypted by SSIS with your Windows account which you use to create a package. So SSIS will fail to decrypt tokens/passwords, when you run […]

How to call Amazon AWS API using SSIS (EC2, Lambda, API Gateway, SQS)

How to call Amazon AWS API using SSIS (EC2, Lambda, API Gateway, SQS)

Introduction In this blog post you will learn how to call Amazon AWS API using SSIS (virtually any API) without a single line of code (No more JAVA, C#, Ruby, Python). Yes you heard it right 🙂 . If you are a SSIS / ETL Developer or even coder everyone loves drag & drop interface. SSIS […]

[Sticky] How to authenticate to an API with OAuth 2.0 using SSIS

[Sticky] How to authenticate to an API with OAuth 2.0 using SSIS

Introduction In the world of REST APIs you have to know how to authenticate, before using any API method. In this article we will cover a topic how to authenticate to an API with OAuth 2.0 technology using SSIS. We are going to use OAuth Connection Manager SSIS component to achieve that, which is a part of ZappySys SSIS […]

<

Copyrights reserved. ZappySys LLC.