Video Tutorial - Integrate Google Calendar data in SSIS
This video covers following and more so watch carefully. After watching this video follow the steps described in this article.
- How to download / install required driver for
Google Calendar integration in SSIS - How to configure connection for
Google Calendar - Features about
API Source (Authentication / Query Language / Examples / Driver UI) - Using
Google Calendar Connection in SSIS
Prerequisites
Before we perform steps listed in this article, you will need to make sure following prerequisites are met:- SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- Make sure ZappySys SSIS PowerPack is downloaded and installed (download it). Check Getting started section for more information.
- Optional (If you want to Deploy and Schedule ) - Deploy and Schedule SSIS Packages
How to read data from Google Calendar in SSIS (Export data)
In this section we will learn how to configure and use Google Calendar Connector in API Source to extract data from Google Calendar.
-
Begin with opening Visual Studio and Create a New Project.
Select Integration Service Project and in new project window set the appropriate name and location for project. And click OK.
-
In the new SSIS project screen you will find the following:
- SSIS ToolBox on left side bar
- Solution Explorer and Property Window on right bar
- Control flow, data flow, event Handlers, Package Explorer in tab windows
- Connection Manager Window in the bottom
Note: If you don't see ZappySys SSIS PowerPack Task or Components in SSIS Toolbox, please refer to this help link. -
Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox. Double click on the Data Flow Task to see Data Flow designer.
-
From the SSIS toolbox drag and API Source (Predefined Templates) on the data flow designer surface, and double click on it to edit it:
-
Select New Connection to create a new connection:
-
Use a preinstalled Google Calendar Connector from Popular Connector List or press Search Online radio button to download Google Calendar Connector. Once downloaded simply use it in the configuration:
Google Calendar -
Proceed with selecting the desired Authentication Type. Then select API Base URL (in most cases default one is the right one). Finally, fill in all the required parameters and set optional parameters if needed. You may press a link Steps to Configure which will help set certain parameters. More info is available in Authentication section.
Steps to get Google Calendar Credentials
To make this work you will have to create an OAuth application. To register an App, perform the following steps (Detailed steps found in the help link at the end):
- Go to Google API Console
- From the Project Dropdown (usually found at the top bar) click Select Project
- On Project Propup click CREATE PROJECT
- Once project is created you can click Select Project to switch the context (You can click on Notification link or Choose from Top Dropdown)
- Click ENABLE APIS AND SERVICES
- Now we need to enable Google Drive API.
- Search Google Drive API. Select and click ENABLE
- Go to back to main screen of Google API Console
- Click OAuth Concent Screen Tab. Enter necessary details and Save.
- Click Credentials Tab
- Click CREATE CREDENTIALS (some where in topbar) and select OAuth Client ID option.
- When prompted Select Application Type as Desktop App and click Create to receive your ClientID and Secret. Later on you can use this information now to configure Connection with UseCustomApp=true.
- Go to OAuth Consent Screen tab. Under Publishing Status click PUBLISH APP to ensure your refresh token doesnt expire often. If you planning to use App for Private use then do not have to worry about Verification Status after Publish.
Google CalendarUser Account [OAuth]https://www.googleapis.com/calendar/v3/Required Parameters UseCustomApp Fill in the parameter... Optional Parameters ClientId Fill in the parameter... ClientSecret Fill in the parameter... Scope Fill in the parameter... RetryMode Fill in the parameter... RetryStatusCodeList Fill in the parameter... RetryCountMax Fill in the parameter... RetryMultiplyWaitTime Fill in the parameter... Redirect URL (Only for Web App) Fill in the parameter... Steps to get Google Calendar Credentials
Use these steps to authenticate as service account rather than Google / GSuite User. Learn more about service account here Basically to call Google API as Service account we need to perform following steps listed in 3 sections (Detailed steps found in the help link at the end)Create Project
First thing is create a Project so we can call Google API. Skip this section if you already have Project (Go to next section)- Go to Google API Console
- From the Project Dropdown (usually found at the top bar) click Select Project
- On Project Propup click CREATE PROJECT
- Once project is created you can click Select Project to switch the context (You can click on Notification link or Choose from Top Dropdown)
- Click ENABLE APIS AND SERVICES
- Now we need to enable API - Google Calendar API
- Search Drive. Select and click ENABLE
Create Service Account
Once Project is created and APIs are enabled we can now create a service account under that project. Service account has its ID which looks like some email ID (not to confuse with Google /Gmail email ID)- Go to Create Service Account
- From the Project Dropdown (usually found at the top bar) click Select Project
- Enter Service account name and Service account description
- For Role, do not select anything for now and Click Continue and then click Done. Next we will create Key.
Create Key
Once service account is created we need to create key file (i.e. credentials).- In the Cloud Console, click the email address for the service account that you created.
- Click Keys.
- Click Add key, then click Create new key.
- Click Create and select P12 format. A P12 key file is downloaded to your computer. We will use this file in our API connection.
- Click Close.
- Now you may use downloaded *.p12 key file as secret file and Service Account Email as Client ID (e.g. some-service-account-name@your-project-id.iam.gserviceaccount.com ).
Add Permission
Now last thing is give read/write permission to Service Account for Calendar you like to access using API.- Copy the email address of your service account we created in previous step (its usually like this some-service-account-name@your-project-id.iam.gserviceaccount.com).
- Login to https://calendar.google.com/calendar and make sure you see the calendar you like to access via API
- On the left, find the "My calendars" section. To expand it, click the Down arrow Down arrow.
- Hover over the calendar you want to share, and click 3 dots next to it. Click "Settings and sharing".
- Find "Share with specific people", Click Add people button and enter the service account email id we obtained in the previous section.
- Select correct permission you like to give for sharing. Click Send button to share. Thats it now we can read / write Calendar Events using Service account approach.
- For more information check this link https://support.google.com/calendar/answer/37082?hl=en
Google CalendarService Account (Using Private Key File) [OAuth]https://www.googleapis.com/calendar/v3/Required Parameters Service Account Email Fill in the parameter... Service Account Private Key Path (i.e. *.p12) Fill in the parameter... Optional Parameters Scope Fill in the parameter... RetryMode Fill in the parameter... RetryStatusCodeList Fill in the parameter... RetryCountMax Fill in the parameter... RetryMultiplyWaitTime Fill in the parameter... -
Select the desired endpoint, change/pass the properties values, and click on Preview Data button to make the API call.
API Source - Google CalendarRead / write Google Calendar data inside your app; perform many Google Calendar operations without coding, just using easy to use high performance API Connector for Google Calendar -
That's it! We are done! Just in a few clicks we configured the call to Google Calendar using Google Calendar Connector.
You can load the source data into your desired destination using the Upsert Destination, which supports SQL Server, PostgreSQL, and Amazon Redshift. We also offer other destinations such as CSV, Excel, Azure Table, Salesforce, and more. You can check out our SSIS PowerPack Tasks and components for more options. (*loaded in Trash Destination)
Load Google Calendar data in SQL Server using Upsert Destination (Insert or Update)
Once you read data from the desired source, now let's see how to Load Google Calendar data in SQL Server using Upsert Destination. Upsert Destination can Merge/Synchronize data from source to target for Microsoft SQL Server, PostgreSql and Redshift. It supports very fast Bulk Upsert (Update or Insert) operation along and Bulk delete.
-
From the SSIS toolbox drag and drop Upsert Destination on the dataflow designer surface
Connect our Source component to Upsert Destination
-
Double click on Upsert Destination component to configure it.
-
Select the desired Microsoft SQL Server/PostgreSql/Redshift Target Connection or click NEW to create new connection. Select Target Table or click NEW to create new table based on source columns.
Configure SSIS Upsert Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS -
Set Action to Upsert => (insert if not matching in target else update). Select Target Connection and Target Table. Check on Insert and Update. Click on Map All to Mappings all columns and check on Only Primary Key columns.
-
Click on OK to save Upsert Destination settings UI.
-
That's it. Run the SSIS Package and it will read the data from the Google Calendar and load the data in the SQL Server/PostgreSql/Redshift using Upsert Destination.
Video Tutorial - Write or lookup data to Google Calendar using SSIS
This video covers following and more so watch carefully. After watching this video follow the steps described in this article.
- How to download SSIS PowerPack for
Google Calendar integration in SSIS - How to configure connection for
Google Calendar - How to Write or lookup data to
Google Calendar - Features about SSIS API Destination
- Using
Google Calendar Connector in SSIS
How to write or lookup data to Google Calendar in SSIS (Import data)
In upper section we learned how to read data, now in this section we will learn how to configure Google Calendar in the API Source to Post data to the Google Calendar.
-
Read the data from the source, being any desired source component. In example we will use ZappySys Dummy Data Source component.
-
From the SSIS Toolbox drag and drop API Destination (Predefined Templates) on the Data Flow Designer surface and connect source component with it, and double click to edit it.
-
Select New Connection to create a new connection:
API Destination - Google CalendarRead / write Google Calendar data inside your app; perform many Google Calendar operations without coding, just using easy to use high performance API Connector for Google Calendar -
Use a preinstalled Google Calendar Connector from Popular Connector List or press Search Online radio button to download Google Calendar Connector. Once downloaded simply use it in the configuration:
Google Calendar -
Proceed with selecting the desired Authentication Type. Then select API Base URL (in most cases default one is the right one). Finally, fill in all the required parameters and set optional parameters if needed. You may press a link Steps to Configure which will help set certain parameters. More info is available in Authentication section.
Steps to get Google Calendar Credentials
To make this work you will have to create an OAuth application. To register an App, perform the following steps (Detailed steps found in the help link at the end):
- Go to Google API Console
- From the Project Dropdown (usually found at the top bar) click Select Project
- On Project Propup click CREATE PROJECT
- Once project is created you can click Select Project to switch the context (You can click on Notification link or Choose from Top Dropdown)
- Click ENABLE APIS AND SERVICES
- Now we need to enable Google Drive API.
- Search Google Drive API. Select and click ENABLE
- Go to back to main screen of Google API Console
- Click OAuth Concent Screen Tab. Enter necessary details and Save.
- Click Credentials Tab
- Click CREATE CREDENTIALS (some where in topbar) and select OAuth Client ID option.
- When prompted Select Application Type as Desktop App and click Create to receive your ClientID and Secret. Later on you can use this information now to configure Connection with UseCustomApp=true.
- Go to OAuth Consent Screen tab. Under Publishing Status click PUBLISH APP to ensure your refresh token doesnt expire often. If you planning to use App for Private use then do not have to worry about Verification Status after Publish.
Google CalendarUser Account [OAuth]https://www.googleapis.com/calendar/v3/Required Parameters UseCustomApp Fill in the parameter... Optional Parameters ClientId Fill in the parameter... ClientSecret Fill in the parameter... Scope Fill in the parameter... RetryMode Fill in the parameter... RetryStatusCodeList Fill in the parameter... RetryCountMax Fill in the parameter... RetryMultiplyWaitTime Fill in the parameter... Redirect URL (Only for Web App) Fill in the parameter... Steps to get Google Calendar Credentials
Use these steps to authenticate as service account rather than Google / GSuite User. Learn more about service account here Basically to call Google API as Service account we need to perform following steps listed in 3 sections (Detailed steps found in the help link at the end)Create Project
First thing is create a Project so we can call Google API. Skip this section if you already have Project (Go to next section)- Go to Google API Console
- From the Project Dropdown (usually found at the top bar) click Select Project
- On Project Propup click CREATE PROJECT
- Once project is created you can click Select Project to switch the context (You can click on Notification link or Choose from Top Dropdown)
- Click ENABLE APIS AND SERVICES
- Now we need to enable API - Google Calendar API
- Search Drive. Select and click ENABLE
Create Service Account
Once Project is created and APIs are enabled we can now create a service account under that project. Service account has its ID which looks like some email ID (not to confuse with Google /Gmail email ID)- Go to Create Service Account
- From the Project Dropdown (usually found at the top bar) click Select Project
- Enter Service account name and Service account description
- For Role, do not select anything for now and Click Continue and then click Done. Next we will create Key.
Create Key
Once service account is created we need to create key file (i.e. credentials).- In the Cloud Console, click the email address for the service account that you created.
- Click Keys.
- Click Add key, then click Create new key.
- Click Create and select P12 format. A P12 key file is downloaded to your computer. We will use this file in our API connection.
- Click Close.
- Now you may use downloaded *.p12 key file as secret file and Service Account Email as Client ID (e.g. some-service-account-name@your-project-id.iam.gserviceaccount.com ).
Add Permission
Now last thing is give read/write permission to Service Account for Calendar you like to access using API.- Copy the email address of your service account we created in previous step (its usually like this some-service-account-name@your-project-id.iam.gserviceaccount.com).
- Login to https://calendar.google.com/calendar and make sure you see the calendar you like to access via API
- On the left, find the "My calendars" section. To expand it, click the Down arrow Down arrow.
- Hover over the calendar you want to share, and click 3 dots next to it. Click "Settings and sharing".
- Find "Share with specific people", Click Add people button and enter the service account email id we obtained in the previous section.
- Select correct permission you like to give for sharing. Click Send button to share. Thats it now we can read / write Calendar Events using Service account approach.
- For more information check this link https://support.google.com/calendar/answer/37082?hl=en
Google CalendarService Account (Using Private Key File) [OAuth]https://www.googleapis.com/calendar/v3/Required Parameters Service Account Email Fill in the parameter... Service Account Private Key Path (i.e. *.p12) Fill in the parameter... Optional Parameters Scope Fill in the parameter... RetryMode Fill in the parameter... RetryStatusCodeList Fill in the parameter... RetryCountMax Fill in the parameter... RetryMultiplyWaitTime Fill in the parameter... -
Select the desired endpoint, change/pass the properties values, and go to the Mappings tab to map the columns.
API Destination - Google CalendarRead / write Google Calendar data inside your app; perform many Google Calendar operations without coding, just using easy to use high performance API Connector for Google Calendar -
Finally, map the desired columns:
API Destination - Google CalendarRead / write Google Calendar data inside your app; perform many Google Calendar operations without coding, just using easy to use high performance API Connector for Google Calendar -
That's it; we successfully configured the POST API Call. In a few clicks we configured the Google Calendar API call using ZappySys Google Calendar Connector
Advanced topics
Actions supported by Google Calendar Connector
Google Calendar Connector support following actions for REST API integration. If some actions are not listed below then you can easily edit Connector file and enhance out of the box functionality.Parameter | Description |
---|---|
CalendarId |
|
Parameter | Description |
---|---|
CalendarId |
|
EventId |
|
Parameter | Description |
---|---|
CalendarId |
|
Parameter | Description |
---|---|
CalendarId |
|
EventId |
|
Parameter | Description |
---|---|
SourceCalendarId |
|
TargetCalendarId |
|
EventId |
|
Parameter | Description |
---|---|
Url |
|
Body |
|
IsMultiPart |
|
Filter |
|
Headers |
|
Conclusion
In this article we discussed how to connect to Google Calendar in SSIS and integrate data without any coding. Click here to Download Google Calendar Connector for SSIS and try yourself see how easy it is. If you still have any question(s) then ask here or simply click on live chat icon below and ask our expert (see bottom-right corner of this page).
Download Google Calendar Connector for SSIS
Documentation
More integrations
Other application integration scenarios for Google Calendar
Other connectors for SSIS
Download Google Calendar Connector for SSIS
Documentation
How to connect Google Calendar in SSIS?
How to get Google Calendar data in SSIS?
How to read Google Calendar data in SSIS?
How to load Google Calendar data in SSIS?
How to import Google Calendar data in SSIS?
How to pull Google Calendar data in SSIS?
How to push data to Google Calendar in SSIS?
How to write data to Google Calendar in SSIS?
How to POST data to Google Calendar in SSIS?
Call Google Calendar API in SSIS
Consume Google Calendar API in SSIS
Google Calendar SSIS Automate
Google Calendar SSIS Integration
Integration Google Calendar in SSIS
Consume real-time Google Calendar data in SSIS
Consume real-time Google Calendar API data in SSIS
Google Calendar ODBC Driver | ODBC Driver for Google Calendar | ODBC Google Calendar Driver | SSIS Google Calendar Source | SSIS Google Calendar Destination
Connect Google Calendar in SSIS
Load Google Calendar in SSIS
Load Google Calendar data in SSIS
Read Google Calendar data in SSIS
Google Calendar API Call in SSIS