CODEWITHSUNDEEP
ssisgoogle-fusion-tables
flying227
flying227

Reputation: 1321

Automatically Update Data in a Google Fusion Table hourly?

We are going to need to update data in a Google Fusion Table hourly and are looking into possibly using an SSIS package to accomplish this.

Has anyone had any experience with updating Google Fusion Tables automatically? Any methods work better than others?

Upvotes: 0

Views: 1547

Answers (1)

billinkc
billinkc

Reputation: 61249

I would decompose your problem into smaller problems until you reach the point you have found one you can solve.

How do I perform a task at X interval

In a pure Windows world, I'd use the native Task Scheduler. It's free and works fine for your scenario of "every hour."

Since SSIS is in the mix, that means you would also have access to SQL Agent. It too is a good fit for your scenario and at this point, I would examine your organization and determine what scheduling tool is predominantly used. It might be "neither."

How can I programmatically update Google Fusion Tables

There is a full Fusion API published. They even have a DML syntax for working with data in a table. However, do observe the warning about using the query syntax for more than 500 rows/10k cells/1MB.

Note: You can list up to 500 INSERT statements, separated by semicolons, in one request as long as the total size of the data does not exceed 1 MB and the total number of table cells being added does not exceed 10,000 cells. If you are inserting a large number of rows, use the import method instead, which will be faster and more reliable than using many SQL INSERT statements.

How can I use SSIS to update Google Fusion Tables

For anything that's not Out Of the Box with SSIS, I usually re-ask the question as "how do I do X in .NET" because that's what it will boil down to. Since it's a web destination, while SSIS has a web service task, it's not as useful as writing your own .NET caller.

I would envision an SSIS package with at least a Data Flow Task. Depending on where your data is coming from, it'd have a source (OLE DB, flat file, etc), any transformations you need between that and the destination. Your destination will be a Script Component configured as a Destination. There you'll use C# or VB.NET to send your Insert/Update commands to the web server. I found this sample of C# that sounds logical. I've never actually used GFT API so I can't comment on whether there's a better route of doing this.

Copying sanya's comment in here

Warning: the attached sample c# script uses Client Login to authenticate against Google. This auth method has been deprecated since April 20, 2012. Usage of OAuth2 is supported.

Upvotes: 3

Related Questions