View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.
SSIS (SQL Server Integration Services) is well known for extracting data from a variety of sources. It can extract data from online sources as well. Google Spreadsheet is one of the online source which stores data online. In this post, we will learn to extract data from Google Spreadsheet using our favourite tool – SSIS.
Before we talk about SSIS package, there are few concepts that we need understand.
#01. To use Google Spreadsheet data you must have a Google account. With a Google account, you can access Google Drive – the place where Gooogle stores the Google Spreadsheets for you.
#02. To connet and interact with Google Spreadsheet data you need, Google Data API SDK. The Google Data API are available in Java, .NET, Python and PHP. The Google Data API are interface between Google online services (ex. Google Spreadsheet, Picasa, Googel Analytics services etc.) and their calling program (client program written in Java, php, python and .NET ). SSIS being a Microsot products supoorts .NET, so you need to download .NET version of Google Data API. This is available at http://code.google.com/p/google-gdata/downloads/list
#03. Once you have downloaded and installed Google Data API SDK, you need to add Google API DLLs into Global Assembly Cache (GAC). To do this, you need to run Visual Studio Command Prompt as adminstrator. You can use gacutil, a commnd line utility to add the DLLS into GAC. Examples are following in the screen shot.