SSIS GoogleAnalyticsSource 1.7

A couple of days ago, Tillmann has released a new version of his amazing SSIS data source for Google Analytics – GoogleAnalyticSource.

After 5 months and some extensive investments here are some of the news that this release contains:

  • Support for SQL Server 2014 – SQL Server 2014 went RTM on the 1st of April 2014 and so it’s support is more than welcome, people are actively migrating to it already
  • Automated Installer – After initial troubles with default Visual Studio Installer, and a couple of months when one would have to install Component manually, this time Tillmann went with Advanced Installer, which seems to do the job as expected. I am more than glad this news, since I am not a big fan of manual installation in the second decade of the 21st Century :)
  • Better Configuration for Metrics and Dimensions – now you can setup File Connection Manager for each of them and this way get better control of their location
  • Automated Extraction for Metrics & Dimension Data – you can update now your Metrics & Dimensions as & when you wish, in a fully automated manner inside SSIS
  • Automated Error Reporting – since this release (1.7), SSIS Google Analytics Source comes with Exceptionless, allowing to configure automated error reporting, which for your privacy has been disabled by default

Let’s review some of the news in the details:

Automated Installer

This is how to do the job of copying the right files into the right place, configuring things, etc:

GoogleAnalyticsInstaller 1GoogleAnalyticsInstaller 2GoogleAnalyticsInstaller 3GoogleAnalyticsInstaller 4

Nuff said :)

Better Configuration for Metrics and Dimensions

GoogleAnalyticsInstaller DownloadsAs you can see on the image on the left, you can download not only the the installation files (.msi) for each of the supported SQL Server versions, but the Dimensions.xml & Metrics.xml as well.

GoogleAnalyics Select Metrics FileConnectionIn the previous release you had to go directly in the interface and specify the location of those files while creating your SSIS package, which made the location static and so in the 1.7 Release we have got opportunity to manage it all through File Connections.

GoogleAnalytics Dimensions FileConnectionManagerYou can simply click on the “New” button and create a new file connection, simply select the location of your file and that’s it!

All the cool perks such as Expressions, possibility to Parametrize your packages, and so on are naturally available. :)

Automated Extraction for Metrics & Dimension Data

Enough people have asked for help updating Metrics & Dimensions, since Google is changing them with the time, and some specifics are not in the default download, and so this time we have a couple of packages that can do those task for you.

Once you download the MetaData package from the SSIS Google Analytics Source page, you will be able to see the following files and a directory for MetaData API, with that folder simply being a SSIS Project that can be opened with Visual Studio 2010 (SSDT BI):

SSIS Google Analytics MetaData APISSIS Google Analytics MetaDataThis package uses Google MetaData API as well as loved by everyones NewtonSoft JSON library.

Inside the ZIP you can find the Full Backup for the Database, it is called GADB.bak – it is around 9MB.

If you are going to use it, you will need to setup the tables & insert some data, so please use the following script:


CREATE TABLE [dbo].[DataType](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[dataType] [nvarchar](255) NULL,
	[dataTypeSsis] [nvarchar](255) NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)
);

CREATE TABLE [dbo].[Dimension](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](255) NULL,
	[uiName] [nvarchar](255) NULL,
	[replacedBy] [nvarchar](255) NULL,
	[type] [nvarchar](255) NULL,
	[group] [int] NULL,
	[status] [int] NULL,
	[dataType] [int] NULL,
	[Length] [int] NULL,
	[Precision] [int] NULL,
	[Scale] [int] NULL,
	[description] [nvarchar](2000) NULL,
	[calculation] [nvarchar](255) NULL,
	[allowedInSegments] [nvarchar](255) NULL,
	[premiumMaxTemplateIndex] [nvarchar](255) NULL,
	[premiumMinTemplateIndex] [nvarchar](255) NULL,
	[maxTemplateIndex] [nvarchar](255) NULL,
	[minTemplateIndex] [nvarchar](255) NULL,
	[appUiName] [nvarchar](255) NULL,
PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)
); 

GO

CREATE TABLE [dbo].[Group](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](255) NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)
);

GO

CREATE TABLE [dbo].[Metric](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](255) NULL,
	[uiName] [nvarchar](255) NULL,
	[replacedBy] [nvarchar](255) NULL,
	[type] [nvarchar](255) NULL,
	[group] [int] NULL,
	[status] [int] NULL,
	[dataType] [int] NULL,
	[Length] [int] NULL,
	[Precision] [int] NULL,
	[Scale] [int] NULL,
	[description] [nvarchar](2000) NULL,
	[calculation] [nvarchar](255) NULL,
	[allowedInSegments] [nvarchar](255) NULL,
	[premiumMaxTemplateIndex] [nvarchar](255) NULL,
	[premiumMinTemplateIndex] [nvarchar](255) NULL,
	[maxTemplateIndex] [nvarchar](255) NULL,
	[minTemplateIndex] [nvarchar](255) NULL,
	[appUiName] [nvarchar](255) NULL,
PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)
);

GO

CREATE TABLE [dbo].[Status](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](255) NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)
);

GO
SET IDENTITY_INSERT [dbo].[DataType] ON;

BEGIN TRANSACTION;
INSERT INTO [dbo].[DataType]([Id], [dataType], [dataTypeSsis])
SELECT 1, N'CURRENCY', N'DT_R8' UNION ALL
SELECT 2, N'FLOAT', N'DT_R8' UNION ALL
SELECT 3, N'INTEGER', N'DT_I8' UNION ALL
SELECT 4, N'PERCENT', N'DT_R8' UNION ALL
SELECT 5, N'STRING', N'DT_WSTR' UNION ALL
SELECT 6, N'TIME', N'DT_WSTR' UNION ALL
SELECT 7, N'STRING', N'DT_WSTR'
COMMIT;
RAISERROR (N'[dbo].[DataType]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO

SET IDENTITY_INSERT [dbo].[DataType] OFF;

You will need to create a default folder where the RAW files shall be stored, check your package parameters (if you have changed the default folder), configure OLEDB Connection Manager to point to your database where you shall be storing extracted information about Dimensions & Metrics and that’s it :)

Import Data.dtsxYou can use “Import Data.dtsx” on the first step to extract the MetaData for Dimensions & Metrics from Google, and then “Export Metrics.dtsx” and “Export Dimensions.dtsx” for extracting that data into respective .xml files.

Should you feel like you do not need or can not store extracted data in the DB, than you can modify the packages in order to do whatever is necessary in your case.

Automated Error Reporting

It is not easy to help a user when no detailed information is available, and so the decision to include Exceptionless into Google Analytics Soucre was made to help determining bugs as soon as the end user decides to activate this functionality.
Exceptionless is a very cool concept, since not only do they offer hosting with some functionality for free and some advanced features for some reasonable money, but they also give you the code to host on your own server if you want it.

Error reporting is disabled by default and you would need to enable the property on the Source in order to start reporting automatically on the errors happening in your system.

By Niko Neugebauer | August 27, 2014 | BI, Business Analytics, business intelligence, oh22, open source, SQL Server, ssis |

36 thoughts on “SSIS GoogleAnalyticsSource 1.7”

  1. Christi

    So how do I get the retrieved OAuth2 token (and updated expiration date) back into the 1.7 version of the GoogleAnalyticsSource task? Without being able to use a Service Account, I’m not having a lot of luck getting this automated via SSIS so I can retrieve GA data into our data warehouse.

  3. Tony

    Hi,

    This is a great connector! I am fairly new to SSIS and have managed to import Google Analytics data into a new SQL table using the v1.7 connector. However I am stuck when I want to set the custom StartDate property of the GA Source Task. What I want to do is bring in new data each day, but also handle if the job fails i.e. set StartDate to be >= the MAX date stored in the SQL table.

    I have tried adding a Date dimension filter but couldn’t do that dynamically or use >= and the same goes for the StartDate custom property. I have tried passing User::StartDate to the custom property, but that didn’t work.

    Can you please give some advice as to how I can just bring in the GA data based on a date variable?

    Thanks,
    Tony

  5. ellen

    Hi Niko,

    Can you please help out to understand why DimensionFilter is not being applied? The original GA filter looks like this:

    ga:eventAction==click;ga:eventLabel==confirm,ga:eventLabel==ready

    and [DimensionFilter] expression is evaluating to this:

    EventAction|ga:eventAction|==|click;EventLabel|ga:eventLabel|==|confirm,EventLabel|ga:eventLabel|==|ready

    No errors during the package processing – the filter is just being disregarded. Any ideas of what I might be missing?

    TIA

    1. Niko Neugebauer Post author

      Hi Ellen,

      everything seems to be looking quite fine.
      Can you share more info about your system ? Feel free to send me an email to niko at nikoport

      Best regards,
      Niko

  6. Alex

    Hi Niko,

    I installed the component, but cant find in the toolbox items. Using VS 2010 GUI SSIS project.

    Please let me know
    Thanks
    Alex

    1. Niko Neugebauer Post author

      Hi Zyan,

      are you using Windows Server?
      If yes, then did you try to log-into Analytics in IE and did you added all of the involved pages to your trusted sites ?

      Best regards,
      Niko

      1. zyan

        Thank you very much!
        The issue has bee solved,but another error popped up,see below

        System.Net.Http.HttpRequestException: An error occurred while sending the request. —> System.Net.WebException: Unable to connect to the remote server —> System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 74.125.193.95:443
        at System.Net.Sockets.Socket.EndConnect(IAsyncResult asyncResult)
        at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Exception& exception)
        — End of inner exception stack trace —
        at System.Net.HttpWebRequest.EndGetResponse(IAsyncResult asyncResult)
        at System.Net.Http.HttpClientHandler.GetResponseCallback(IAsyncResult ar)
        — End of inner exception stack trace —
        at Google.Apis.Requests.ClientServiceRequest`1.Execute()
        at SSISComponents.Authenticator.Login()

        Please help me once more,thank you very much

        1. zyan

          Hi Niko
          above issue has gone after my several attempts,I guess it’s due to my slow connection.
          Anyway,thank you very much!

        2. zyan

          Hi Niko
          Above issue has been gone after my several attempts,I guess,it’s due to my slow connection.
          Anyway,thank you very much!

  8. Rosita V. Simmons

    Hello Niko!
    I installed SSIS GoogleAnalyticsSource version 1.7 beta on my pc. However, the GoogleAnalyticsSource component does not show up in my SSIS toolbox under the Common tab for a Data flow task (nor anywhere else). I have done several uninstalls/reinstalls and repairs of the component and rebooted my pc numerous times. I’m running Visual Studio Ultimate 2013 (Update 4), SQL Server Data Tools Business Intelligence for Visual Studio 2013, and Microsoft SQL Server 2012 on a Windows 8.1 Enterprise machine. The SSIS GoogleAnalyticsSource dlls are in the C:\Program Files (x86)\Microsoft SQL Server\110\DTS\PipelineComponents folder. Even though I thought the component installation msi would register the dlls, I still registered the dlls in the global assembly cache (gac) using the gacutil in the VS 2012 x86 Native Tools Command Prompt and verified the dlls are in the gac. So far, all my troubleshooting efforts have been unsuccessfull and I am at my wit’s end. Please help…thanks!

    1. Niko Neugebauer Post author

      Hi Rosita,

      I think you are using wrong version of Visual Studio – for SQL Server 2012 you will need to be using SSDT-BI which corresponds to Visual Studio 2012. Visual Studio 2013 SSDT-BI is compatible with SQL Server 2014.

      Best regards,
      Niko

  9. Andrew

    Hi,

    I too have tried installing the plugin however I am having exactly the same issue as above. Can anyone help resolve?

    Cheers,

    Andrew

  10. Joey

    I just upgraded to the newer version of this component as it looks like the standard auth that was supported in 1.2 is no longer supported by google as of yesterday. I think I’m missing something fundamental though. I used the wizard to login to GA and got an OAuth2 token and expiration but I run this as a scheduled job. Will I need to get the token in an automated fashion each time this job runs? If so, can anyone point to a method for automating that?

    Thanks in advance.

    Joe

    1. Charlie

      Hi Joe,
      Did you find a solution for this? I’m having the same issue and since I’m running from a SQL Server Agent job I will need the service account to authenticate. I was curious if you have the same issue? Thanks for any info…

      Charlie

  11. Kan

    I have the same issue as above. I installed SSIS GoogleAnalyticsSource version 1.7 beta on my dev server but it does not show up in SSIS toolbox under Data flow task. I use Visual Studio 2013 SSDT-BI and Microsoft SQL Server 2014 on a Windows Server 2012 R2 Standard machine.
    Please help…thanks!

  12. Nguyen Le

    I can’t install .NET 4.5 on my server where this will deploy to, anybody know how I could use this with .NET 4.0?

    I tried downloading the source code but it seems I’m missing references. Should I try version 1.6 instead?

    Thank you very much.

      1. Nguyen

        Thank you. I sent him an email a couple days ago and was hoping for a quicker answer if I also ask here since some other might be in the same boat and could offer help.

        1. Niko Neugebauer Post author

          Sure thing. :)
          I believe that Tillmann is right now very busy with other projects and so further development and support of Google Analytics is a kind of on hold.

  13. Frank Block

    Hi, we’re using GA Premium and would like to use the SSIS component to extract GA data into our DWH. Unfortunately we’re seeing differences in the data extracted by the SSIS GA component from what GA shows us via their web interface. Apparently this is related to data sampling. How can we make the SSIS GA component extract unsampled data ?
    thanks for any hints and help
    Frank

  14. Fred

    Hi Good day,

    I have used this version 1.7 and I noticed that more than 90% of my pageloadtime or server response time is 0. Do you know why?,

    I mean these 2 metrics I mentioned is very important to me and I am wondering why Google Analytics data has mostly 0 on those metrics.

    Thanks,
    Fred

  15. Jorge Ramirez

    Hi Niko,
    My name is Jorge, I’m from Lima Peru, I installed this controller for sql 2014 using windows 10 as OS but as soon as I add the controller in the data flow and i click Sig In i get this error:

    System.TypeInitializationException: The type initializer for ‘DotNetOpenAuth.Logger’ threw an exception. —> System.ArgumentOutOfRangeException: Length cannot be less than zero.
    Parameter name: length
    at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
    at System.Reflection.RuntimeAssembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
    at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
    at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection)
    at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
    at System.Reflection.Assembly.Load(String assemblyString)
    at DotNetOpenAuth.Loggers.Log4NetLogger.get_IsLog4NetPresent()
    at DotNetOpenAuth.Loggers.Log4NetLogger.Initialize(String name)
    at DotNetOpenAuth.Logger.InitializeFacade(String name)
    at DotNetOpenAuth.Logger.Create(String name)
    at DotNetOpenAuth.Logger..cctor()
    — End of inner exception stack trace —
    at DotNetOpenAuth.Messaging.Reflection.MessageDescription.ReflectMessageType()
    at DotNetOpenAuth.Messaging.Reflection.MessageDescription..ctor(Type messageType, Version messageVersion)
    at DotNetOpenAuth.Messaging.Reflection.MessageDescriptionCollection.Get(Type messageType, Version messageVersion)
    at DotNetOpenAuth.Messaging.StandardMessageFactoryChannel.c__DisplayClass3.b__2(Version version, Type messageType)
    at System.Linq.Enumerable.d__22`3.MoveNext()
    at System.Collections.Generic.List`1.InsertRange(Int32 index, IEnumerable`1 collection)
    at DotNetOpenAuth.Messaging.StandardMessageFactoryChannel.GetMessageDescriptions(ICollection`1 messageTypes, ICollection`1 versions, MessageDescriptionCollection descriptionsCache)
    at DotNetOpenAuth.Messaging.StandardMessageFactoryChannel..ctor(ICollection`1 messageTypes, ICollection`1 versions, IChannelBindingElement[] bindingElements)
    at DotNetOpenAuth.OAuth2.ClientBase..ctor(AuthorizationServerDescription authorizationServer, String clientIdentifier, String clientSecret)
    at Google.Apis.Authentication.OAuth2.DotNetOpenAuth.NativeApplicationClient..ctor(AuthorizationServerDescription authorizationServer)
    at SSISComponents.Authenticator.Login()

    1. Niko Neugebauer Post author

      Hi Jorge,

      I have no idea about this error, please post it on the forum of the component – and maybe the author will be able to find time to help you.

      Best regards,
      Niko Neugebauer

  16. komal

    hi nik,
    I get this Error , how can i resolve it?
    Dfk_LoadDeviceData:Error: The managed pipeline component “SSISComponents.GoogleAnalyticsSource, SSISComponents.Dts.Pipeline.GoogleAnalyticsSource2014, Version=1.7.0.0, Culture=neutral, PublicKeyToken=1eab8a67122c49ca” could not be loaded. The exception was: Could not load file or assembly ‘SSISComponents.Dts.Pipeline.GoogleAnalyticsSource2014, Version=1.7.0.0, Culture=neutral, PublicKeyToken=1eab8a67122c49ca’ or one of its dependencies. The system cannot find the file specified..