Google Analytics for SSIS (SQL Server Integration Services)

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

Google Analytics 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

Screen Shot 2014-05-19 at 21.32.48In 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

Screen Shot 2014-05-19 at 20.25.53Google 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”).

GoogleAnalyticsSourceInside 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.

Google Analytics Source OptionsAfter 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:
Google Analytics Login

After successful login, you will need to authorise the data sharing for the plugin by clicking on the “Accept” button:
Screen Shot 2014-05-19 at 23.59.29

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

Basic Configuration

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.

Date DimensionFor 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.

Google Analytics MetricsFrom 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.

Screen Shot 2014-05-20 at 00.42.13Before 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.

SSIS GoogleAnalyticsSource at workAt the end, I simply added a OleDBDestination to my DataFlow, connected and mapped it to the provided and executed the package.

Enjoy SSIS GoogleAnalyticsSource !

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

  1. Mathías

    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 author

      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

        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 author

          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

  2. JG

    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 author

      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

  3. Charlie Hoadley

    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 author

      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

        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?

  4. Armando

    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 author

      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

  5. Miguel

    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 author

      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.

  6. Megha

    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. Niko Neugebauer Post author

      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

  7. Walter

    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. komal

      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

  8. RBS

    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

  9. manoj kumar s

    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 author

      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

  10. Rob

    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

      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

  11. John

    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

  12. Jacob

    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?

  13. Kaushal Solanki

    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?

  14. Aung

    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?

  15. Robin

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

    Any ideas?

  16. komal

    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?

  17. jahnavi

    hi Nik,
    My pacakge runs fine in visual studio. It fails if I schedule pacakge. is there any solution for this?

    1. komal

      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

      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. komal

          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 author

            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

  18. Raghav

    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?