Access Google Analytics API 3.0 using Oauth2.0 in SSIS

Access Google Analytics API 3.0 using Oauth2.0 in SSIS

Steps to using a service account to access the API, as example

Generate a service account key in your Google API Console:

  1. Go to the Google API Console.
  2. Click on the “API Access” tab.
  3. Click on “Create client ID…”

img1

    1. Select “Service account” as the “Application type” and click on “Create client ID”.

img2

    1. Download your private key and store it in a safe place that only you can access.

img3

    1. A new section called “Service account” should appear under your API Access information. Ensure this page is handy as you will need to copy the “Email address” later on.

img4

Using service accounts and assertion flow with Google OAuth 2.0 services require that you have your own domain registered with Google Apps. This is because user impersonation is controlled at the domain level with no finer granularity of access control. In other words, all users of a domain using a service account that has been white listed with the power to impersonate, can impersonate any user in the domain. For example, this is why Gmail accounts cannot be impersonated with service accounts.

Security concerns

Because of Google Apps domain-level control, it is important that you protect the *.p12 key file that allows a service account to access the Google services for which it has been authorized. This is especially the case since we are going to be granting that service account the ability to impersonate any user in the domain. It is also a good practice to allow service accounts to only access one Google API each (using the “scope” field).

Steps to login in Accounts:

Screenshots: (http://www.google.co.in/analytics/)

Login to above mentioned URL:

img5


Need to add Client Id-Email as a user for each account in
Users Section with all the required permission like Administrator.

SSIS Code to Access Google Analytics API 3.0

Below is the code to access Google Analytics in SSIS using script task:

We need to create below variable to store information about AccountID and AccountName with respect to google analytics accounts.

e.g; AccountID : ga:306787, AccountName : http://www.abc.com

  1. User::var_client_password : This will store the .p12 key path.
  2. User::var_client_username : This will store the email address which has been created in Service account.
  3. User::var_Ed_date : We can pass any date filter.
  4. User::var_src_file: Path of the source where we need to export csv files from Google Analytics.
  5. User::var_st_date : We can pass any date filter.
  6. User::var_table_id : It will store AccountID like ga:12222

We need to add below version-3 dll(s) references in script task to access Google Analytics API:

  1. DotNetOpenAuth
  2. Google.Apis
  3. Google.Apis.Analytics.v3
  4. Google.Apis.Authentication.OAuth2
  5. Newtonsoft.Json.Net35
  6. System.Core

 Steps:

Step 1: Access the Google Analytics data

Step 2: Export the data into csv files based on AccountID(s).

Step 3: We can use CSV files as source to load in database using SSIS.

Below is the source code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using DotNetOpenAuth.OAuth2;

using Google.Apis.Analytics.v3;

using Google.Apis.Analytics.v3.Data;

using Google.Apis.Authentication.OAuth2;

using Google.Apis.Authentication.OAuth2.DotNetOpenAuth;

using Google.Apis.Services;

using Google.Apis.Util;

using System.Security.Cryptography.X509Certificates;

using System.IO;

using Google.Apis.Json;

String Client_ID = string.Empty;

String Key_Path = string.Empty;

String Account_ID = string.Empty;

String strstartdate = string.Empty;

String strenddate = string.Empty;

String strsrcfilepath = string.Empty;

publicvoid Main()

{

strstartdate = Convert.ToDateTime(Dts.Variables[“var_st_date”].Value).ToString(“yyyy-MM-dd”);

strenddate = Convert.ToDateTime(Dts.Variables[“var_Ed_date”].Value).ToString(“yyyy-MM-dd”);

Client_ID = Dts.Variables[“var_client_username”].Value.ToString();

Key_Path = Dts.Variables[“var_client_password”].Value.ToString();

Account_ID = Dts.Variables[“var_table_id”].Value.ToString();

strsrcfilepath = Dts.Variables[“var_src_file”].Value.ToString();

//This is the API url which we’re storing to a string

string scope = AnalyticsService.Scopes.AnalyticsReadonly.GetStringValue();

//It is the email address

//that you have added as a user to your Analytics account

string client_Email = Client_ID;

//This is the physical path to the file we downloaded earlier

//To demonstrate this, I’ve kept the full path to my key file.

string key_file = Key_Path;

//The password Google gives you, probably the same as the one below

string key_pass = “notasecret”;

//Store the authentication description

AuthorizationServerDescription desc = GoogleAuthenticationServer.Description;

//Create a certificate object to use when authenticating

X509Certificate2 key = newX509Certificate2(key_file, key_pass, X509KeyStorageFlags.Exportable);

//Now, we will log in and authenticate, passing in the description

//and key from above, then setting the accountId and scope

AssertionFlowClient client = new AssertionFlowClient(desc, key) { ServiceAccountId = client_Email, Scope = scope };

//Finally, complete the authentication process

OAuth2Authenticator<AssertionFlowClient> auth = new OAuth2Authenticator<AssertionFlowClient>(client, AssertionFlowClient.GetState);

//First, create a new service object

AnalyticsService gas = new AnalyticsService(new BaseClientService.Initializer() { Authenticator = auth });

//Create our query

//The Data.Ga.Get needs the parameters:

//Analytics account id, starting with ga:

//Start date in format YYYY-MM-DD

//End date in format YYYY-MM-DD

//A string specifying the metrics

DataResource.GaResource.GetRequest r = gas.Data.Ga.Get(Account_ID, strstartdate, strenddate, “ga:Visitors,ga:visits”);

//Specify some addition query parameters

r.Dimensions = “ga:date”;

r.MaxResults = 10000;

//Execute and fetch the results of our query

GaData d = r.Fetch();

DataTable dt = newDataTable();

dt.Columns.Add(“ga:date”);

dt.Columns.Add(“ga:visitors”);

dt.Columns.Add(“ga:visits”);

if (d.TotalResults > 0)

{

foreach (var row in d.Rows)

{

DataRow dr = dt.NewRow();

dr[“ga:date”] = row[0];

dr[“ga:visitors”] = row[1];

dr[“ga:visits”] = row[2];

dt.Rows.Add(dr);

}

}

WriteToCSV(dt);

Dts.TaskResult = (int)ScriptResults.Success;

}

publicvoid WriteToCSV(DataTable dt)

{

// Create the CSV file to which grid data will be exported.

StreamWriter sw = newStreamWriter(strsrcfilepath, false);

// First we will write the headers.

//DataTable dt = m_dsProducts.Tables[0];

int iColCount = dt.Columns.Count;

for (int i = 0; i < iColCount; i++)

{

sw.Write(dt.Columns[i]);

if (i < iColCount – 1)

{

sw.Write(“~”);

}

}

sw.Write(sw.NewLine);

// Now write all the rows.

foreach (DataRow dr in dt.Rows)

{

for (int i = 0; i < iColCount; i++)

{

if (!Convert.IsDBNull(dr[i]))

{

sw.Write(dr[i].ToString());

}

if (i < iColCount – 1)

{

sw.Write(“~”);

}

}

sw.Write(sw.NewLine);

}

sw.Close();

}

Advertisements

About jeeveshfuloria

i am working in iSpace global solution pvt. ltd. in MSBI field and pursuing b.tech through AMIE.
This entry was posted in SSIS. Bookmark the permalink.

13 Responses to Access Google Analytics API 3.0 using Oauth2.0 in SSIS

  1. Pingback: gAnalytics WordPress Plugin (Utilities) | FreeWPremium

  2. matteo says:

    not work with api 1.6.
    this code go in error: AuthorizationServerDescription desc = GoogleAuthenticationServer.Description;

  3. Thanks for sharing, I’m able to get your code running in an console application but not as a SSIS script task. It resolves the assemblies but won’t build and complains on namespaces “Services”, “Auth” and “Json” are missing in namespace “Google.Apis”. The weird thing is that using Google.Apis.Util and Google.Apis.Analytics.v3 work just fine but not Google.Apis.Services and Auth and Json. I’ve added dll:s in GAC and reference the dll:s in a project folder. Do you have any suggestion how you do it?

  4. Pingback: Gregory Smith

  5. Gonnu says:

    Hey, Thanks for your tutorial ! I’ve got a question : how did you manage to register using Google.Apis.Authentication.OAuth2 to the gac ? Looks like SSIS requires to register into the GAC any third party library. Could you confirm ? (gacutil.exe /i doesn’t work as it’s a .NET 4.0 library…) Thanks a lot !

  6. Nguyen says:

    Does this still work? I’m trying to follow this but got an error on the very first important line of code: string scope = AnalyticsService.Scope.AnalyticsReadonly.ToString();

    CA0001 Error Running Code Analysis CA0001 : The following error was encountered while reading module ‘Google.Apis’: Could not resolve type reference: [mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]System.Runtime.CompilerServices.IAsyncStateMachine. [Errors and Warnings] (Global)

    I’m using Visual Studio 2012. I’ve spent weeks doing this so any help is appreciated.

    Thank you.
    Nguyen

  7. Is it OK to share on Linkedin? I like what you guys are usually up too.
    Keep up the terrific work!

  8. Jerin Sam says:

    It’s working!!! Great !!
    Is DotNetOpenAuth a third party dll?

  9. Wonderful post! We will be linking to this particularly great post on our website.
    Keep up the great writing.

  10. Nayan Patel says:

    Ahhh.. C# scripts… Hope Microsoft make something native for useful integration scenarios like this. I was npot able to get anything working so far except this one http://zappysys.com/products/ssis-powerpack/ssis-google-analytics-source-connector/

    Any one know how to create *.p12 certificate file so I don’t have to use OAuth because I don’t have userid and password of Google Account for which customer want to display data.

  11. Sql Daddy says:

    Hello,
    Really helpful. I am happy to see the mentioned response that more people were searching for the right solution and get help from your post. I would like to than you. Here I also want to say that I have also more customized package for different-different work like Generate SSRS Reports in SSIS ,ssis generate ssrs report ,ssis rest api, ssis xml ,ssis delete old files,ssis dynamodb ,ssis amazon s3 . In all package we have mentioned proper work with demo . Here I would like to mentione a sample package where We have mentioned that how to read data from Google Analytics Service without learning complex API .There are more features of this package .
    Interested people can follow this link .
    http://zappysys.com/products/ssis-powerpack/ssis-google-analytics-source-connector/
    Hope It will be helpful.

  12. This site certainly has all the information and facts I wanted concerning this subject and didn’t know who to ask.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s