2

I want to use Google.Apis.AnalyticsReporting.v4 to ETL the google analytics data to our data warehouse. It is worthy to mention that I use SSIS (Microsoft Integration Service) and use Script Component (C#) as task to ETL aforementioned data.

I found this task very hard and find these references,

Use Google Analytics API to show information in C#

It used Google.Apis.Analytics.v3 for doing this and it is not V4. First of all, I create service account,

https://cloud.google.com/iam/docs/creating-managing-service-accounts#iam-service-accounts-rename-console

and save service account email and .p12 key, after that I use this method,

Analytics Reporting v4 with API key

that use Google.Apis.AnalyticsReporting.v4 but many references have errored and they are old, such using Google.Apis.Services;

I confused and want to know the code for ETL the GA data by C#. Appreciate to help me on this issue. I want to ETL metrics like "session" and dimension like "campaign"

Pouyan Nikzad
  • 19
  • 1
  • 8

2 Answers2

1

I was never able to get it to work using a scripting component because of the .net version of the dlls. The library dlls are around .net 4.5 and as far as far as i could see most versions of MS Sql server dont support higher then .net 4.0. You also have to figure out where the dlls need to be in order for ssis to find them. If your going to get it to work your going to need all the dlls from the library as you cant use nuget install. i would start with the basic tutorial get that working as a console application then copy it over to SSIS and put that into your scripting component you need all the using and their corresponding dlls there is a lot of them.

Google apis.services isnt old its the auth part of the library it just hasnt needed to be changed.

custom component

What I have done was create a custom component to connect to Google analytics. The main issue I had was the dlls and the .net framework. I was never able to use the Google .net client library. I was only able to get this working by manually creating my own Google analytics library targeting .net 3.5 and using Oauth2 rather than service account.

Coding my own service account authentication code has so far escaped me. I beleave i have a very old open question here on stack asking how to get it to work.

Community
  • 1
  • 1
Linda Lawton - DaImTo
  • 106,405
  • 32
  • 180
  • 449
  • So you mean it is not possible to use google library and ssis component together. so we must create a custom component. do you think it is possible to create a .net project and store GA data then use ssis to etl that stored data? – Pouyan Nikzad Sep 21 '19 at 09:54
  • I haven't tried in a few years and not with the current version of ssis it could be that it supports .net 4.6 your just going to have to give it a try. If not you're going to have to create your own code and do it all your self. Google Targit Google analytics I was the lead developer on that project. I wouldn't recommend using it though the project died after I left the company – Linda Lawton - DaImTo Sep 22 '19 at 20:25
1

Disclaimer: I work at ZappySys

Calling API is possible in SSIS using Script Task by referencing SDK dlls but you have to consider two things.

  1. How much effort needed to code this flow. Some times its not worth it to develop your own if any one already developed such component (Free or Paid).
  2. Is SDK provided by Google is compatible with SSIS version? Each SSIS version supports specific .net version(s). So you must use Google SDK compatible with that. For Example if you like to code this for SSIS 2012 then you must use SDK which is coded in .net 4.0 or Lower version. Using latest SDK might not work sometimes if its coded in .net 4.6 for example.

Here is the list of supported .net versions by various SQL Server / SSIS

  • SSIS / SQL Server 2012 - Use .net 4.0
  • SSIS / SQL Server 2014 - Use .net 4.5 or lower
  • SSIS / SQL Server 2017 - Use .net 4.6.0 or lower
  • SSIS / SQL Server 2019 - Use .net 4.6.2 or lower

If you do not want to go through hassle you may consider 3rd party components such as ZappySys Google Analytics Source for SSIS

ZappySys
  • 91
  • 3