Google Analytics for SSIS (SQL Server Integration Services)

Update: There is a new 1.7 Version of Google Analytics Source for SSIS

In the modern age of the technology, almost everyone owns a site or two, and most certainly every business does. Every site is a major source of data, data which can be used for better understanding of the visitors and their preferences. Analytics.

In my opinion, there are 2 basic types of analytics platforms that people are using on the web currently – Google Analytics and others.
Under others I mean that the overall popularity of the first one made them all to be in the 2nd category. This all includes some very interesting open source platforms, such as Piwik (http://piwik.org)

Using Google Analytics via Web Browser is a very common thing, and a lot of people do that on a daily (and some on hourly basis), and so playing & doing basic analysis of the information inside Google Analytics interface is a very accessible & easy thing.
For those looking to export analysed data, it is easy to do with an “export” button which allows to have data exported into CSV, TSV, TSF for Excel, Excel, Google Spreadsheets, as well as into PDF.
This is really awesome, isn’t it ? :)

Well, my answer is yes & no. I can’t imagine anyone wanting to go into Google Analytics interface daily in order to export that information into CSV, copy it into some folder, open your preferred integration tool (mine is definitely SSIS :)), and then execute process for integrating this data into their Analytical platform or DataWarehouse.

Would not it be nice, if there would be an automated plugin for automating those tasks allowing you to configure this type of information once and then simply import it into your preferred system ?

Guess what – there is a such plugin:
SSIS GoogleAnalyticsSource is the plugin that you might have been searching desperately!

Developed by my colleague Tillmann Eitelberg from OH22 in his own free time, this is one more cool gift for the global community.

SSIS GoogleAnalyitcsSource is an open source plugin for SSIS which allows you to read data inside your Data Flow directly from Google Analytics.

SSIS GoogleAnalyitcsSource is using oAuth 2.0 for authentication with Google Analytics, which is a kind of standard at this moment.

Installation

Unfortunately, for the current version 1.6 Beta there is no automated installer.
How could this happen you might have ask â€”Â the answer is simple: there were problem with automated installer, which are being solved and I am sure that the final release won’t have any of the determined problems

In the mean time you can easily download the DLLs, install & configure them:
You will need to register with you GACUtil.exe all those dlls, by invoking a Command Prompt as Administrator,
note that in my case all DLLs were extracted to “C:\Install\GoogleAnalytics\” folder:
gacutil.exe -i InstallShield c:\install\GoogleAnalytics\DotNetOpenAuth.dll gacutil.exe -i InstallShield c:\install\GoogleAnalytics\Google.Apis.Analytics.v3.dll gacutil.exe -i InstallShield c:\install\GoogleAnalytics\Google.Apis.Authentication.OAuth2.dll gacutil.exe -i InstallShield c:\install\GoogleAnalytics\Google.Apis.dll gacutil.exe -i InstallShield c:\install\GoogleAnalytics\Newtonsoft.Json.dll gacutil.exe -i InstallShield c:\install\GoogleAnalytics\SSISComponents.Dts.Pipeline.GoogleAnalyticsSource.dll gacutil.exe -i InstallShield c:\install\GoogleAnalytics\Zlib.Portable.dll

As a next step, simply copy all of those files into “C:\Program Files (x86)\Microsoft SQL Server\110\DTS\PipelineComponents\” folder of you installation. (In my case I am using SQL Server 2012, if you are using a different version, you will need to adjust the path accordingly to the version number).

You are all set!

Usage

Google Analytics for SSIS Source is really easy to use: after creating a new .dtsx file, let us go and add a new DataFlow component as shown on the picture on the left (I named it “Read GoogleAnalytics”).

Inside our DataFlow, add a new Source Element – GoogleAnalyticsSource, as shown on the picture. It has a distinctive green icon which will help you to recognise it.

After double click on our Source, you will be presented with the following screen of options as it can be seen on the left.

First thing to do is to authorise the component for reading data out of the Google Analytics engine – click on the “Sign in” button to be presented a login window for Google Account, such as shown on the screen below:

After successful login, you will need to authorise the data sharing for the plugin by clicking on the “Accept” button:

You will return back to the previous configuration screen where the “Google oAuth” group shall have all data already filled out for you:

Basic Configuration

As you can see on my basic configuration screen, the Access Token is already filled out, so the only things I have to select/change are Account, WebProperty and Profile.

Account is your Google Account that you are logged in, If you are logged into multiple accounts at the same time, you will be able to select the one you will be using for the Google Analytics data extraction.
WebProperty is basically the site you are extracting data from, and
Profile is the configured profile at the Google Analytics.

There is a good amount of options available at the current version, which I might describe one day in a later post, but for the beginning we need to select 2 things: Dimensions & Metrics.

For this exercise I will simply select the Date as my primary Dimension. My results will be delivered in a “YYYYMMDD” format which I can easily transform in SSIS into any format I need at my destination.

We are not limited just by 1 Dimension and we can select multiple, but things will progressively become more complicated from here. One absolutely awesome detail about this screen is that every single Dimension has a description text at the bottom of the Dialog (with yellow background) and so it is enough to click on the Dimension without selecting it in order to obtain the detailed information of what type of information you will be served with.

From the Metrics perspective I will simply select the number of Page Views as shown on the screen.

This will allow me to get the detailed information on the amount of Page Views that my website was getting per day. As in the case of Dimensions I am not limited at just 1 Metric, I can actually go and select multiple, and oh my – there are so many of them that are listed at your disposure.

Before advancing any further, you have an option of selecting preview of the data you will be getting – and for that you just need to click on the “Preview” button at the bottom of the Dialog screen. If you need you can select a different sample from the default (200 rows).

You will be served 1 more Dialog screen where you will be able to see the first 200 (or any other value you choose) rows, that you can scroll and browse, making sure that this is exactly what you expect.
Note: Take a good look at the headers, you will see that they are named according to the ga:dimension or ga: convention. Those are the names that will be flowing through you DataFlow by default, but of course you can alter them by opening “Show Advanced Editor” and editing respective outputs. :)

After verifying all the data, click on the “Close” button and then on the “Ok” at the main Dialog screen to return back to the DataFlow editing.

At the end, I simply added a OleDBDestination to my DataFlow, connected and mapped it to the provided and executed the package.

Enjoy SSIS GoogleAnalyticsSource !

By Niko Neugebauer | May 20, 2014 | BI, Business Analytics, business intelligence, oh22, SQL Server, ssis |

47 thoughts on “Google Analytics for SSIS (SQL Server Integration Services)”

MathÃas May 29, 2014 at 1:28 pm

Hi Niko, I followed all your steps here but I can’t find “GoogleAnalyticsSource” as Source Element, did you know what can be wrong ? Thanks
1. Niko Neugebauer Post authorMay 29, 2014 at 5:02 pm
  
  Hi MathÃas,
  
  Thank you for the comment!
  -Did you registered all DLL’s with the respective GACUtil ?
  -Did you copy them into C:\Program Files (x86)\Microsoft SQL Server\110\DTS\PipelineComponents\” (This path is valid for SQL Server 2012)
  – Did you add a DataFlow and checked your “Common” Group? It is placed there, not under “Sources” Components.
  1. Toby July 16, 2014 at 11:05 am
    
    Hi,
    
    I’m having the same issue here. I can’t find the “Common” Group. I’m using BIDs which I think is running VS2008.
    
    All other steps are complete.
    
    Any idea where I can find this source element?
    
    T
    1. Niko Neugebauer Post authorJuly 21, 2014 at 10:12 pm
      
      Hi Toby,
      
      I never tried using VS2008, I have had on those VM’s only VS 2010.
      I do not think that VS2008 is compatible with SQL Server 2012.
      
      Best regards,
      Niko Neugebauer
JG June 4, 2014 at 11:17 am

Hi Niko,
First of all thank you for your post and your explanation regarding Google Analytics V3 installation.
Following your instructions, I’ve been able to install the component on a platform running SQL Server 2012.
However, I did not achieve to install it on SQL Server 2008 R2. In your previous comment, you stated: “(This path is valid for SQL Server 2012)”. Does that mean that replacing 110 in the path with 100 would do the job for SQL Server 2008 R2?

On the codeplex page of the project it’s mentionned: “Since Version 1.0.1.0 the SSIS GoogleAnalytics Source is available for SQL Server 2008 (R2) x86 and x64.”

I’m not German speaker but I had a look at Tillman’s post on his website:
http://ssis-components.net/index.php/2014/03/google-analytics-v3/
and it’s stated:
“Die neue Version steht fÃ¼r x86/x64 fÃ¼r den SQL Server 2012 zur VerfÃ¼gung. Eine Version fÃ¼r den SQL Server 2008 ist derzeit nicht in Planung. ”

Could you please confirm that this version of the component IS NOT compatible with SQL Server 2008 even if in the project description on CodePlex we can read:
Project Description
A SSIS Data Source Component for SQL Server 2008 and SQL Server 2012 to load data from the Google Data API ??

Thank you
1. Niko Neugebauer Post authorJune 5, 2014 at 2:32 pm
  
  Hi JG,
  
  The current version of Google Analytics supports SQL Server 2012 only (it should work with SQL Server 2014 as well).
  It is not compatible with SQL Server 2008 R2 and previous, but from my conversation with Tillmann there is no reason for not recompiling a new version it for SQL Server 2008 R2 for example. The issue has to do with the lack of time.
  
  Best regards,
  Niko Neugebauer
Charlie Hoadley June 18, 2014 at 11:22 pm

Hi Niko,
Your blog is extremely helpful and insightful! Thanks!

Quick question, do you know if Google Analytics for SSIS provides the dimensions and metrics for “Mobile App Analytics”? It seems that I’m only seeing the Google Analytics standard web offerings. I’m after a dimension named Screen Name and metrics named New Users, Screen Views. Thanks for any info!
1. Niko Neugebauer Post authorJune 20, 2014 at 10:32 am
  
  Hi Charlie,
  
  thanks for the comment.
  So far I know the list of the Metrics & Dimensions is not complete just because this data is changing with the time (Google is keep on evolving & developing the platform),
  and so quite a limited list of Metrics & Dimensions is provided. The current lists are presented in 2 files – Dimension.xml & Metrics.xml (https://analyticssource.codeplex.com/releases/view/119547),
  if you need an extra metrics just update it by including the metrics you are looking for.
  The idea is that with the time those files shall be updated at the CodePlex site, but you can simply go & edit your own file on your PC.
  
  The current Google Analytics reference can be found here:
  https://developers.google.com/analytics/devguides/reporting/core/dimsmets
  Just check out ga:mobileDeviceBranding
  
  Best regards,
  Niko
  1. Charlie Hoadley June 20, 2014 at 4:41 pm
    
    Great, thank you Niko. I did check out the .xml files and seems fairly straight forward to add a metric or dimension. However, when editing the file I didn’t understand where I can obtain the ? From Google?
    1. Charlie Hoadley June 20, 2014 at 4:42 pm
      
      obtaining the ID :)
      1. Niko Neugebauer Post authorJune 21, 2014 at 11:04 pm
        
        Hi Charlie,
        
        you just need a random ID which should be unique within your Dimensions/Metrics.
        
        Regards,
        Niko
Armando July 25, 2014 at 1:53 pm

Hi Niko,
Your blog is very good! Congratulations!

Quick question, do you have the Google Analytics for SSIS for Sql Server 2008 R2?
My system is 2008 R2. :(

Do you can grant for we?

Thanks
Armando
1. Niko Neugebauer Post authorJuly 26, 2014 at 9:39 am
  
  Hi Armando,
  
  unfortunately I do not have a version for 2008 R2,
  I hope somebody with enough time shall produce this version.
  
  Best regards,
  Niko Neugebauer
Miguel November 5, 2014 at 10:45 pm

Hi Niko,

Thanks for your help.

I follow your steps and managed to log in, with automatically fulfilling the Access Token and all the other parameters, then I changed the WebProperty to Website however I can’t get any Dimensions and Metrics populated, did I missed anything here?
1. Niko Neugebauer Post authorNovember 17, 2014 at 8:26 am
  
  Hi Miguel,
  
  I believe you have not specified the files with Dimensions & Metrics,
  this setting can be found at the bottom of the very first configuration tab.
  You will need to setup a File Connection Manager, and only after that you will have the list of available options.
Megha December 16, 2014 at 12:13 pm

HI,
For the above version of Google Analytics Source , How we can provide Authentication ( Username and password) Dynamically . Because here i am finding only Token And Expiration date only.
If you will see old version there we will be having UserName and Password option and we can provide by using variable (dynamically). What about current version ?

For Old version u can check here :
http://ssis-components.net/image.axd?picture=2011%2f2%2fSSISGA_001.jpg

Regards,
MeghaV
1. Megha December 16, 2014 at 12:15 pm
  
  And the thing is every time it generates new token .
2. Niko Neugebauer Post authorDecember 17, 2014 at 10:00 am
  
  Hi Yudhi,
  
  According to Tillmann, that since new version is using OAuth â€” there is no User/Password anymore and parametrising is not supported.
  
  Best regards,
  Niko
Walter December 29, 2014 at 9:12 pm

Hi Niko,
First of all, thank you for your post.
I’ve followed your instructions and everything worked well, exept with a single metric: “sessionDuration”
With which I get the following error when i try with “Preview” :

“System.ArgumentNullException: ‘dataType’ argument cannot be null.
Parameter name: dataType
at System.Data.DataColumn..ctor(String columnName, Type dataType, String expr, MappingType type)
at System.Data.DataColumn..ctor(String columnName, Type dataType)
at SSISComponents.DataTypes.GaDataToDataTable(GaData googleData)
at SSISComponents.FrmGoogleAnalyticsSource.btnQuery_Click(Object sender, EventArgs e)”

I’ve tried modifing the Metric.xml file but it didn’t work, could you please give me some guidance or suggestion about that?
1. Niko Neugebauer Post authorDecember 30, 2014 at 2:12 am
  
  Hi Walter,
  
  did you try to filter out only the sessions that have engagement hits (https://support.google.com/analytics/answer/1006253?hl=en) ?
  Does the error still occur?
  Also, consider posting your problem to http://analyticssource.codeplex.com/discussions/576068, so that Tillmann can see and maybe answer on it himself.
  
  Best regards,
  Niko
zyan January 24, 2015 at 9:11 am

Hi Niko
I downloaded SQL Server 2012 version’s msi,when I clicked ‘Sign In’,it threw a following script error when authorising data sharing for the plugin:
Line:51
Char:3511
Error:’Iso’ is undefined
Code:0
URL:https://accounts.google.com/o/oauth2/auth?response_type=code&client_id=398409649931.apps.googleusercontent.com&redirect_uri=urn:ietf:wg:oauth:2.0:oob&scope=https

please help me,thank you in advance!
Ravi February 21, 2015 at 3:24 am

Is there a way to pass user variable to the start/end dates?
1. Ravi February 21, 2015 at 3:35 am
  
  Nevermind….figured it out. I was able to do it in the Data Flow Properties->Expressions.
  1. Kaushal Solanki January 19, 2016 at 6:59 am
    
    Hi Ravi,
    
    can you please explain in brief how you have pass start date and end date.
2. komal May 26, 2016 at 5:18 pm
  
  hi Ravi…I am trying to achieve the same. Pass the dates as variables. Can you post your screen shots . It will be helpful
  Thanks,
  komal
RBS May 21, 2015 at 6:40 pm

Niko
Using 2012 msi, I was able to login and choose my account, web property and profile. I also created new file connections one for dim and another for metric, then when I go to dimension and metric tabs, I don’t see any options listed. Do you know why? How can I get all those dimension and metric options populated?

Thanks in advance
1. Niko Neugebauer Post authorMay 22, 2015 at 2:40 pm
  
  Hi Rohan,
  
  you will need to download dimensional and metrics files – http://analyticssource.codeplex.com/releases/view/129281
  
  Best regards,
  Niko
manoj kumar s June 3, 2015 at 7:17 am

Hi Niko,

I have installed google analytic source-2014 msi and downloaded dim n metrics files also but yet I cant see google analytic s source attached in my visual studio.
Please help me out.
1. Niko Neugebauer Post authorJune 8, 2015 at 11:18 am
  
  Hi Manoj,
  
  did you try to write to Tillmann (author of the plugin) ?
  I have heard from him, that there are some breaking changes from Google …
  
  Regards,
  Niko
Rob July 21, 2015 at 3:00 pm

Hi,

I have been using the component for a number of years and found it to be fantastic – that was until Google changed to require OAuth2.0

I use VS2013 (although I have other versions if required) and SQL Server2008R2 – but I cannot get the install to work and show up in any version of Visual Studio – my understanding is that this is because I use SQL 2008R2 and not SQL2012 or higher.

Has anybody managed to get this component working with OAuth2.0 and SQL Server 2008R2?

Thanks,
1. John August 21, 2015 at 9:39 am
  
  Hi Rob!
  
  I have the same problem, cant seem to get my hands on or make Google.Apis.Auth.OAuth2; with SSIS 2008 to work…
  Did you manage to make it work?
  
  Jon
John August 21, 2015 at 9:36 am

Hi!

Do you have something similar for Google Spreadsheets for SSIS for SQL Server 2008 R2?
I am trying to find Google.Apis.Auth.OAuth2; for this but seems like it cant work with .net framework 3.5?

I would appreciate any info!

Jon
Jacob November 9, 2015 at 3:48 pm

I have installed the 1.7 beta version for sql server 2014 in combination with sql server data tools for visual studio 2013. The plugin isn’t visible in the ssis toolbox after the installation.
How can I manually fix this?
Kaushal Solanki March 3, 2016 at 7:34 am

Want to fetch combination of dimensions(Page,page level 1, page level 2, page level 3 ,date and pageview ) with Metric(pageview) but my pageview metric count is coming wrong so can any one help me out on this?
Aung May 3, 2016 at 8:08 pm

Hi Niko, Thanks for the Google Analytics Source. It is very useful and helpful to my project.
I got a quick question, everything is fine except for the dimension filter. May I know any solution to be able to use the filter?
1. Niko Neugebauer Post authorMay 25, 2016 at 4:13 pm
  
  Hi Aung,
  
  please ask the author at the plugin page about this.
  
  Best regards,
  Niko
Robin May 19, 2016 at 2:42 pm

System.Threading.Tasks.TaskCanceledException: A task was canceled.
at Google.Apis.Requests.ClientServiceRequest`1.Execute()
at SSISComponents.Authenticator.Login()

Any ideas?
1. Niko Neugebauer Post authorMay 25, 2016 at 4:15 pm
  
  nope, sorry.
komal May 26, 2016 at 5:22 pm

Hi Niko,
I installed the GA source successfully. My question is can we loop through the accounts in the Googleanlaytics site to get the dimensions?
Example: My google accounts has 3 websites:
I need to loop through all the 3 websites to get the same dimensions. Is it possible?
jahnavi June 22, 2016 at 2:57 pm

hi Nik,
My pacakge runs fine in visual studio. It fails if I schedule pacakge. is there any solution for this?
1. komal August 31, 2016 at 6:02 pm
  
  I have visual studio 2012 , Google anlaytical source 2012 msi and sql server 2014. It works find in visual studio but does not work when ran from sql server and also from job. Is there any solution please help.
  Got struck with this issue from two months and there is no one who really replies for the issue :( sad.
2. komal August 31, 2016 at 6:02 pm
  
  I have visual studio 2012 , Google anlaytical source 2012 msi and sql server 2014. It works fine in visual studio but does not work when ran from sql server and also from job. Is there any solution please help.
  Got struck with this issue from two months and there is no one who really replies for the issue :( sad.
  1. Niko Neugebauer Post authorAugust 31, 2016 at 7:17 pm
    
    Hi komal,
    
    if you read the previous comments – I have already pointed to post your doubts to Tillmann, who developed the component: http://analyticssource.codeplex.com
    
    Best regards,
    Niko Neugebauer
    1. komal August 31, 2016 at 7:46 pm
      
      Nik, Atleast you reply , but it posted my issue long back on the site , no one replied. I feel if tillman ever sees any comments now :(. If you any idea which version of GA msi goes with SQL and visual studio version . Please respond.
      1. Niko Neugebauer Post authorSeptember 1, 2016 at 4:56 pm
        
        Hi komal, sorry i don’t have an idea. Tillmann has released that code as a free & open source. He has no obligation of supporting it. Maybe you should take over and develop it further ?
        
        Best regards,
        Niko
Raghav January 11, 2017 at 3:26 pm

Hello Niko,
Thanks for the amazing post. i was able to achieve what i wanted to. just one quick question. If I schedule my SSIS package to run daily does the OAuth token refreshes automatically after expiry?
1. Niko Neugebauer Post authorJanuary 11, 2017 at 6:36 pm
  
  Hi Raqhav,
  
  I do not think so,
  but please ask Tillmann, the author of the tool.
  
  Best regards,
  Niko Neugebauer