Google Data Connection Manager
 PreviousNext

Google Data Connection Manager can be used along with Google Analytics Source to communicate with Google API Services such as Google Analytics API. This connection manager handles complex authorization flow to obtain initial Token and renewing token automatically once its expired.

Download SSIS PowerPack

Content

Video Tutorial

Step-By-Step

In this tutorial you will learn how to create Google Data Connection. You can create multiple connection.
  1. Firstly, You need to Download and Install SSIS ZappySys PowerPack.
  2. Once you finished above steps, Open Visual Studio and Create New SSIS Package Project.
  3. Right click in the Connection Manager panel and click on New Connection.
    SSIS Create New Connection
  4. Select ZS-GOOGLE-DATA Connection Manager from the Connection Managers list and Click on Add Button.
    SSIS Google Data Connection - Add
  5. Now, in Connection Manager, set Authentication type to OAuth 2.0, checked on default OAuth App and click on Generate OAuth Token.
    Google Data Connection Manager UI
  6. Click on Test Connection to check configure correct or not.
  7. Click on OK to save Google Data Connection manager setting UI.
  8. Once you finished, you will see Google Data connection in Connection Manager panel.
    Google Data Connection Manager - Success

Properties

Property Name Description
AppVer
ServiceType

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>
GoogleAnalytics [1] Google Analytics
AdWords [2] AdWords
ServiceAccountEmail
IgnoreSSLCertificateErrors
CertificatePath
CertificateAccessMode

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

Option Description
P12File [0] PKCS 12 - From File
ConnectionString
RetainSameConnection
DeveloperToken DeveloperToken is used with certain Google APIs (e.g. Google AdWords). Ignore this if API doesn't require it.
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.
SupportPassword
RetryMode Error retry mode. Default is do not retry (None).

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 for any web, io, timeout errors
RetryWhenStatusCodeMatch [2] Retry for matching response status code(s) (e.g. 429|501)
RetryWhenMessageMatch [3] Retry if error message contains specified string(s) (e.g. nessage1|message2)
RetryCountMax Maximum number of retry attempts to try before throwing error. This option is ignored if RetryMode is set to None (Do not retry).
RetryMessageList This option retries on a  static retry_wait_time you entered using this formula. new_retry_wait_time = entered retry_wait_time * current_retry_attempt_counter (after each failed attempt attempt_counter is increased). So for example if your retry wait time is set to 3000ms (3sec) then with this option turned on you will see wait as follow: On 1st attempt = 3000*1 wait time= 3000ms, On 2nd attempt 3000*2=6000ms wait time, On 3rd attempt 3000*3=9000ms wait time... so on. This option is ignored if RetryMode is set to None (Do not retry).
RetryMultiplyWaitTime This option increases wait time on each retry attempt using this formula. new_retry_wait_time = specified retry_wait_time * retry_attempt_counter (after each failed attempt retry_attempt_counter is increased by one). So for example if your retry wait time is set to 3000ms (3sec) then with this option turned on you will see wait as follow: On 1st attempt = 3000*1= 3000ms wait time, On 2nd attempt 3000*2=6000ms wait time, On 3rd attempt 3000*3=9000ms wait time... so on until max attempts reached. This option is ignored if RetryMode is set to None (Do not retry).
RetryStatusCodeList Specifies a list of http status codes on which you like to retry. This option can be used if you want to narrow down retry attempts on a very specific list of http status codes separated by pipe rather than any random web exception. Use pipe to separate values e.g. 429|500 so on to retry for only these 3 status codes rather than any web exceptions. Refer to your API documentations to findout which errors you can retry on. This option is ignored if RetryMode is set to None (Do not retry).
RetryWaitTimeMs This value specifies how much time to wait (in milliseconds) before trying again on error(s). This option is ignored if RetryMode is set to None (Do not retry).
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
ConnectByProxy

Setting UI

Google Data Connection Manager - Setting UI

See Also

References

Copyrights reserved. ZappySys LLC.