May 9, 2012 at 4:07 am
Hi All
I was just wondering if anyone has ever done a Google Analytics import to SQL via SSIS?
A quick google brought up some C# scripts, a PHP web scraper and using the java client to export to CSV and then import it, but was wondering if anyone has managed to query the API and load it direct in SSIS to the DB?
This was a task which was done a couple of years ago and I have been asked to re-ignite the flame to get it moving again and the previous way was to use xp_cmdshell to execute a file which loads it to CSV and imports it, which I have no trouble with, just wondering if anyone has a better solution.
Thanks
May 9, 2012 at 5:32 am
It would definitely be more elegant to use the Google Analytics API (this has .Net libraries you can download and it looks pretty simple to interface with), then use a script task source to stream the data in SSIS. You'll need to register the Google Analytics API library in the GAC to reference it in a script task.
Sounds like fun, but if the current method ain't broke, there's nothing fundamentally wrong with running an executable to do it (although I'd find another method than xp_cmdshell)
May 9, 2012 at 5:37 am
HowardW (5/9/2012)
It would definitely be more elegant to use the Google Analytics API (this has .Net libraries you can download and it looks pretty simple to interface with), then use a script task source to stream the data in SSIS. You'll need to register the Google Analytics API library in the GAC to reference it in a script task.Sounds like fun, but if the current method ain't broke, there's nothing fundamentally wrong with running an executable to do it (although I'd find another method than xp_cmdshell)
Thanks Howard, I'm not a developer by any means but sounds like a good task to get my teeth into for a start of my first C or VB dev'ing.
You share the same concern as me in relation to using xp_cmdshell to execute things, maybe could run this in a process task instead of calling it via an execute sql task. My ultimate goal would be to query the API's direct as thats what they are there for at the end of the day. Now to just keep digging and get a working solution.
May 9, 2012 at 6:42 am
After changing my search terms fo SSIS Google Analytics some kind developer has written a plugin for GA which can be found here http://analyticssource.codeplex.com/. All you need to do is add the component as a toolbox item, then within a DFT you can select a GA source from the control flow section of SSIS and hey presto jobs a good'un as they say.
January 7, 2013 at 2:09 am
I've made a start with the SSIS DFT task that's mentioned on "http://analyticssource.codeplex.com/".
I can get the task to login to my google analytics account, and can successfully preview the data prior to passing this on to any subsequent tasks.
However, when I come to execute the dft, no rows actually flow out of the task which is really confusing.
The Tillmann GA task is version 1.2.
SQL SERVER 2008 r2 (RTM Version 10.50.1600.1)
Help !!!
January 7, 2013 at 2:13 am
Not all metrics can be exported in 1 DFT, its trial and error, I started with 1 metric then added one in at a time until it wouldnt export then you know which one you need to do in a seperate DFT.
January 7, 2013 at 2:15 am
The Codeplex solution Looks very nice.
Out of curiosity what are you using google analytics for, is it a matter of data enrichment (Geo codes etc) or is there some other reason behind it.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 7, 2013 at 2:29 am
We are using it to track statistics for our SaaS product, where we are developing a series of SSRS reports where the tennant can slice and dice the information as they wish.
Time on site, new visitors, visits etc so the tennant can see how active the site is for the money they pay for the service.
January 7, 2013 at 2:34 am
Thanks Anthony, I was curious as I've been thinking of using the Google API to get Geo-spatial data for locations, as well as using the google data with a 2012 DQ solution and wondered how you found the performance of the API.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 7, 2013 at 2:38 am
The geo data would be in a different API, so couldn't comment on that particular API, but the GA API using the codeplex solution works great, apart from the metrics issue.
Unsure if there is a different codeplex solution to take advantage of that API.
January 7, 2013 at 2:39 am
Thanks anthony, I'm sure there will be one I'll do some digging.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 7, 2013 at 5:47 am
anthony.green (1/7/2013)
Not all metrics can be exported in 1 DFT, its trial and error, I started with 1 metric then added one in at a time until it wouldnt export then you know which one you need to do in a seperate DFT.
I found that "https://developers.google.com/analytics/devguides/reporting/core/dimsmets?hl=de" looks great for seeing which dimensions will export with the related metrics.
I can't get his task to output any rows though, even though I can "preview" the data within the task - that's the most fustration part.
Did you have to tweak with any config files in c:\program files to get the task to conntact to your google analytics account intially? (I have to tell it to use a proxy server to connect).
I'm stumpted as to why I can't see any output rows, but I can preview them !?!
January 7, 2013 at 5:51 am
Nope works out the box for me, no proxy account or anything needed as we are not behind a proxy.
Start a discussion on the codeplex site, someone might know how to solve your issue.
January 7, 2013 at 6:05 am
Cheers - already raised a "ticket/issue" http://analyticssource.codeplex.com/workitem/15933
but if it's just Tillmann working on the project then it might be a while for any answer.
I'll ask our DBA to give the install a try, if it works "out of the box" for you, then it might be some weird privilege thing that's specific to my work profile.
Thanks for your time.
June 7, 2013 at 5:04 pm
I installed this and works fine so far. No special tweaking needed. It looks like a fine tool. Thanks for the link Anthony.
Cheers
----------------------------------------------------
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply