{"id":798,"date":"2016-09-24T23:21:11","date_gmt":"2016-09-24T23:21:11","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=798"},"modified":"2025-03-19T05:34:24","modified_gmt":"2025-03-19T05:34:24","slug":"get-data-google-spreadsheet-using-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/get-data-google-spreadsheet-using-ssis\/","title":{"rendered":"How to read \/ write Google SpreadSheet using SSIS"},"content":{"rendered":"

Introduction<\/h2>\n
UPDATE:<\/strong>\u00a0ZappySys has released a brand new API Connector for Google Sheets Online<\/a> which makes it much simpler to\u00a0Read\/Write Google Sheets Data in SSIS<\/strong> compared to the steps listed in this article. You can still use steps from this article but if you are new to API or want to avoid learning curve with API then use newer approach.<\/p>\n

Please visit this page to see all<\/a>\u00a0Pre-Configured ready to use API connectors which you can use in SSIS API Source<\/a> \/ SSIS API Destination<\/a> OR\u00a0API ODBC Driver<\/a> (for non-SSIS Apps such as Excel, Power BI, Informatica).<\/p>\n<\/div><\/div>\n\"\"<\/a>In this post you will learn how to get data from Google SpreadSheet<\/em> (REST API) using SSIS. We will use drag and drop REST API connectors from SSIS PowerPack<\/a>. No need to download any SDK or learn programming language (e.g. JAVA, C#, Ruby, Python) when you use SSIS PowerPack Connectors. We will use Google Drive API and Google Sheets API to get file list and export SpreadSheet as CSV file in few clicks (Using OAuth 2.0 connection in SSIS).<\/p>\n

In this tutorial we will use REST API Task<\/a> to call some ad-hoc API (e.g. get File List from Google Drive) and save output into Variable or File. We will use OAuth connection<\/a> along with\u00a0ZappySys JSON Source connector<\/a> to read data from Google SpreadSheet (Use Drive API) and load into SQL Server (Export Google SpreadSheet to CSV).<\/p>\n

Create Google API Project<\/h2>\n

First step to access any Google API is create an API Project in Google Console. If you don’t want to go through this then Skip this\u00a0Step-1 and in the next section select Default OAuth App option on OAth Connection Manager (This is the most easiest option for now unless you want to use your own OAuth App).<\/p>\n

Check this article<\/a> for step-by-step instructions.\u00a0When you follow these instructions make sure you enable Google Drive API (In the article it shows how to enable YouTube API as an example but you will need to enable Drive API for this article).<\/p>\n

Create OAuth Connection Manager in SSIS<\/h2>\n

Once you create Google API project and obtained Client ID and Client Secret your next step is to create OAuth Connection Manager<\/a> in SSIS. ZappySys OAuth connection manager comes with many predefined OAuth Providers (e.g. Facebook, Twitter, Google etc) but you can also define custom OAuth settings for any OAuth enabled API.<\/p>\n

To create SSIS OAuth 2.0 Connection for Google API perform following steps.<\/p>\n

    \n
  1. Download and Install SSIS PowerPack<\/li>\n
  2. Create new SSIS Package<\/li>\n
  3. Right click in Connection Manager Area and Click “New Connection”<\/li>\n
  4. When prompted select ZS-OAUTH connection type<\/li>\n
  5. On the OAuth Connection Manager Select Provider=Google Sheets + Drive<\/strong>.<\/li>\n
  6. You can keep “Use Default App” selected or choose Custom App. Click here to learn – how to register Custom Google App<\/a><\/li>\n
  7. If you choose the Custom App option then enter the below scopes<\/strong> (or click Select Scopes). If you choose the default app then it’s not required.\n

    This will allow read \/ write access to Drive Files or Sheet (E.g. Reading file content \/ Export file to CSV). You can use Scope browser to see many other available permissions.<\/p>\n

    https:\/\/www.googleapis.com\/auth\/drive\r\nhttps:\/\/www.googleapis.com\/auth\/drive.file\r\nhttps:\/\/www.googleapis.com\/auth\/drive.readonly\r\nhttps:\/\/www.googleapis.com\/auth\/spreadsheets\r\nhttps:\/\/www.googleapis.com\/auth\/spreadsheets.readonly<\/pre>\n
    Click Generate Token button.<\/p>\n
    \"SSIS<\/a>
    SSIS OAuth Connection Manager – Access Google Drive API using OAuth 2.0<\/p><\/div><\/li>\n
  8. You will see UI as below… Click Accept (You may have to scroll to see that button sometimes)<\/li>\n
  9. Click Test to see connection is working.<\/li>\n<\/ol>\n
    NOTE:<\/strong> If you don’t want to use Default OAuth App provided by ZappySys then select “Use Custom OAuth App” option (App created in previous section) specify your ClientID, ClientSecret.<\/p>\n
    Find Google Sheet ID for API call<\/h2>\n
    Before we can read or write Sheet data in SSIS we need to know Google Driver File ID. We will use this ID in next few sections.<\/p>\n
    Easiest way to find File ID is look at the URL like below. If you need to know ID at runtime dynamically then refer last section.<\/p>\n
     <\/p>\n
    \"Get<\/a>
    Get Google Drive File ID for API call (Sheet ID)<\/p><\/div>\n
    Reading Google SpreadSheet Data in SSIS<\/h2>\n
    Basically there are two ways you can export \/ read Google SpreadSheet data.<\/p>\n