Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Google Analytics SQL Import Expand / Collapse
Author
Message
Posted Wednesday, May 9, 2012 4:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:10 AM
Points: 5,221, Visits: 5,119
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




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1296996
Posted Wednesday, May 9, 2012 5:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 3, 2014 8:05 AM
Points: 1,191, Visits: 9,892
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)
Post #1297031
Posted Wednesday, May 9, 2012 5:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:10 AM
Points: 5,221, Visits: 5,119
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.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1297032
Posted Wednesday, May 9, 2012 6:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:10 AM
Points: 5,221, Visits: 5,119
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.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1297059
Posted Monday, January 7, 2013 2:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 6, 2014 7:25 AM
Points: 48, Visits: 144
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 !!!
Post #1403486
Posted Monday, January 7, 2013 2:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:10 AM
Points: 5,221, Visits: 5,119
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.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1403487
Posted Monday, January 7, 2013 2:15 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:31 AM
Points: 922, Visits: 2,524
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
Post #1403488
Posted Monday, January 7, 2013 2:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:10 AM
Points: 5,221, Visits: 5,119
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.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1403495
Posted Monday, January 7, 2013 2:34 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:31 AM
Points: 922, Visits: 2,524
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
Post #1403496
Posted Monday, January 7, 2013 2:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:10 AM
Points: 5,221, Visits: 5,119
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.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1403498
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse