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:

Nuff said

Better Configuration for Metrics and Dimensions

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

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

You 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):

This 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

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