<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 - General
»
Google Analytics SQL Import
14 posts, Page 1 of 2
1
2
»»
Google Analytics SQL Import
Rate Topic
Display Mode
Topic Options
Author
Message
anthony.green
anthony.green
Posted Wednesday, May 09, 2012 4:07 AM
SSCertifiable
Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075,
Visits: 4,831
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
HowardW
HowardW
Posted Wednesday, May 09, 2012 5:32 AM
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, April 30, 2013 5:55 AM
Points: 1,034,
Visits: 7,660
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
anthony.green
anthony.green
Posted Wednesday, May 09, 2012 5:37 AM
SSCertifiable
Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075,
Visits: 4,831
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
anthony.green
anthony.green
Posted Wednesday, May 09, 2012 6:42 AM
SSCertifiable
Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075,
Visits: 4,831
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
Fai Ho Fu
Fai Ho Fu
Posted Monday, January 07, 2013 2:09 AM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:06 AM
Points: 48,
Visits: 119
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
anthony.green
anthony.green
Posted Monday, January 07, 2013 2:13 AM
SSCertifiable
Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075,
Visits: 4,831
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
Jason-299789
Jason-299789
Posted Monday, January 07, 2013 2:15 AM
SSC Eights!
Group: General Forum Members
Last Login: Friday, May 03, 2013 5:35 AM
Points: 803,
Visits: 2,122
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
anthony.green
anthony.green
Posted Monday, January 07, 2013 2:29 AM
SSCertifiable
Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075,
Visits: 4,831
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
Jason-299789
Jason-299789
Posted Monday, January 07, 2013 2:34 AM
SSC Eights!
Group: General Forum Members
Last Login: Friday, May 03, 2013 5:35 AM
Points: 803,
Visits: 2,122
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
anthony.green
anthony.green
Posted Monday, January 07, 2013 2:38 AM
SSCertifiable
Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075,
Visits: 4,831
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 »
14 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
