Google BigQuery Connector for SSIS

Read / write Google BigQuery data inside your app without coding using easy to use high performance API Connector
You can use this connector to integrate Google BigQuery data inside SSIS and SQL Server. Let's take a look at the steps below to see how exactly to accomplish that.

Download Documentation

Connect to Google BigQuery in other apps
Google BigQuery Connector Connect Google BigQuery in SQL Server SQL Server
Connect to Google BigQuery in SQL Server
Google BigQuery Connector Connect Google BigQuery in Power BI Power BI
Connect to Google BigQuery in Power BI
Google BigQuery Connector Connect Google BigQuery in SSRS SSRS
Connect to Google BigQuery in SSRS
Google BigQuery Connector Connect Google BigQuery in Informatica Informatica
Connect to Google BigQuery in Informatica
Google BigQuery Connector Connect Google BigQuery in MS Access MS Access
Connect to Google BigQuery in MS Access
Google BigQuery Connector Connect Google BigQuery in MS Excel MS Excel
Connect to Google BigQuery in MS Excel
Google BigQuery Connector Connect Google BigQuery in SSAS SSAS
Connect to Google BigQuery in SSAS
Google BigQuery Connector Connect Google BigQuery in C# C#
Connect to Google BigQuery in C#
Google BigQuery Connector Connect Google BigQuery in Python Python
Connect to Google BigQuery in Python
Google BigQuery Connector Connect Google BigQuery in JAVA JAVA
Connect to Google BigQuery in JAVA
Google BigQuery Connector Connect Google BigQuery in Tableau Tableau
Connect to Google BigQuery in Tableau
Google BigQuery Connector Connect Google BigQuery in SAP Crystal Reports SAP Crystal Reports
Connect to Google BigQuery in SAP Crystal Reports
Google BigQuery Connector Connect Google BigQuery in Azure Data Factory (Pipeline) Azure Data Factory (Pipeline)
Connect to Google BigQuery in Azure Data Factory (Pipeline)
Google BigQuery Connector Connect Google BigQuery in Azure Data Factory (SSIS) Azure Data Factory (SSIS)
Connect to Google BigQuery in Azure Data Factory (SSIS)
Google BigQuery Connector Connect Google BigQuery in Talend Studio Talend Studio
Connect to Google BigQuery in Talend Studio
Google BigQuery Connector Connect Google BigQuery in UiPath UiPath
Connect to Google BigQuery in UiPath
Google BigQuery Connector Connect Google BigQuery in PowerShell PowerShell
Connect to Google BigQuery in PowerShell
Google BigQuery Connector Connect Google BigQuery in ODBC ODBC
Connect to Google BigQuery in ODBC

Video Tutorial - Integrate Google BigQuery 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 BigQuery integration in SSIS
  • How to configure connection for Google BigQuery
  • Features about API Source (Authentication / Query Language / Examples / Driver UI)
  • Using Google BigQuery Connection in SSIS

Prerequisites

Before we perform steps listed in this article, you will need to make sure following prerequisites are met:
  1. SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
  2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
  3. Make sure ZappySys SSIS PowerPack is downloaded and installed (download it). Check Getting started section for more information.

Read data from Google BigQuery in SSIS (Export data)

In this section we will learn how to configure and use Google BigQuery Connector in API Source to extract data from Google BigQuery.

  1. Begin with opening Visual Studio and Create a New Project.

  2. Select Integration Service Project and in new project window set the appropriate name and location for project. And click OK.

  3. In the new SSIS project screen you will find the following:

    1. SSIS ToolBox on left side bar
    2. Solution Explorer and Property Window on right bar
    3. Control flow, data flow, event Handlers, Package Explorer in tab windows
    4. Connection Manager Window in the bottom
    SSIS Project Screen
    Note: If you don't see ZappySys SSIS PowerPack Task or Components in SSIS Toolbox, please refer to this help link.

  4. Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox. Double click on the Data Flow Task to see Data Flow designer.
    SSIS Data Flow Task - Drag and Drop

  5. From the SSIS toolbox drag and API Source (Predefined Templates) on the data flow designer surface, and double click on it to edit it:
    SSIS API Source (Predefined Templates) - Drag and Drop

  6. Select New Connection to create a new connection:
    API Source - New Connection

  7. Use a preinstalled Google BigQuery Connector from Popular Connector List or press Search Online radio button to download Google BigQuery Connector. Once downloaded simply use it in the configuration:

    Google BigQuery
    Google BigQuery Connector Selection

  8. 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.

     User Account [OAuth]

    User accounts represent a developer, administrator, or any other person who interacts with Google APIs and services. User accounts are managed as Google Accounts, either with Google Workspace or Cloud Identity. They can also be user accounts that are managed by a third-party identity provider and federated with Workforce Identity Federation. [API reference]

    Steps how to get and use Google BigQuery credentials

    Follow these steps on how to create Client Credentials (User Account principle) to authenticate and access BigQuery API in SSIS package or ODBC data source:

    WARNING: If you are planning to automate processes, we recommend that you use a Service Account authentication method. In case, you still need to use User Account, then make sure you use a system/generic account (e.g. automation@my-company.com). When you use a personal account which is tied to a specific employee profile and that employee leaves the company, the token may become invalid and any automated processes using that token will start to fail.

    Step-1: Create project

    This step is optional, if you already have a project in Google Cloud and can use it. However, if you don't, proceed with these simple steps to create one:

    1. First of all, go to Google API Console.

    2. Then click Select a project button and then click NEW PROJECT button:

      Start creating a new project in Google Cloud

    3. Name your project and click CREATE button:

      Create a new project in Google Cloud

    4. Wait until the project is created:

      Wait until project is created in Google Cloud
    5. Done! Let's proceed to the next step.

    Step-2: Enable Google Cloud APIs

    In this step we will enable BigQuery API and Cloud Resource Manager API:

    1. Select your project on the top bar:

      Select project in Google Cloud

    2. Then click the "hamburger" icon on the top left and access APIs & Services:

      Access APIs and services in Google Cloud

    3. Now let's enable several APIs by clicking ENABLE APIS AND SERVICES button:

      Enable API for project in Google Cloud

    4. In the search bar search for bigquery api and then locate and select BigQuery API:

      Search for API in Google Cloud

    5. If BigQuery API is not enabled, enable it:

      Enable Google BigQuery API

    6. Then repeat the step and enable Cloud Resource Manager API as well:

      Enable Cloud Resource Manager API
    7. Done! Let's proceed to the next step.

    Step-3: Create OAuth application

    1. First of all, click the "hamburger" icon on the top left and then hit VIEW ALL PRODUCTS:

      View all products in Google Cloud

    2. Then access Google Auth Platform to start creating an OAuth application:

      Open Google Auth Platform in Google Cloud

    3. Start by pressing GET STARTED button:

      Start creating an app in Google Cloud

    4. Next, continue by filling in App name and User support email fields:

      Fill app info in Google Cloud

    5. Choose Internal option, if it's enabled, otherwise select External:

      Choose app audience in Google Cloud

    6. Optional step if you used Internal option in the previous step. Nevertheless, if you had to use External option, then click ADD USERS to add a user:

      Add test user in Google Cloud app

    7. Then add your contact Email address:

      Enter app contact info in Google Cloud

    8. Finally, check the checkbox and click CREATE button:

      Create app in Google Cloud
    9. Done! Let's create Client Credentials in the next step.

    Step-4: Create Client Credentials

    1. In Google Auth Platform, select Clients menu item and click CREATE CLIENT button:

      Start creating app client in Google Cloud

    2. Choose Desktop app as Application type and name your credentials:

      Create OAuth app client in Google Cloud

    3. Continue by opening the created credentials:

      View app client credentials in Google Cloud

    4. Finally, copy Client ID and Client secret for the later step:

      Use client ID and secret to read Google REST API data

    5. Done! We have all the data needed for authentication, let's proceed to the last step!

    Step-5: Configure connection

    1. Now go to SSIS package or ODBC data source and use previously copied values in User Account authentication configuration:

      • In the ClientId field paste the Client ID value.
      • In the ClientSecret field paste the Client secret value.

    2. Press Generate Token button to generate Access and Refresh Tokens.

    3. Then choose ProjectId from the drop down menu.

    4. Continue by choosing DatasetId from the drop down menu.

    5. Finally, click Test Connection to confirm the connection is working.

    6. Done! Now you are ready to use Google BigQuery Connector!


    Configuring authentication parameters
    Google BigQuery
    User Account [OAuth]
    https://www.googleapis.com/bigquery/v2
    Required Parameters
    UseCustomApp Fill-in the parameter...
    ProjectId (Choose after [Generate Token] clicked) Fill-in the parameter...
    DatasetId (Choose after [Generate Token] clicked and ProjectId selected) Fill-in the parameter...
    Optional Parameters
    ClientId
    ClientSecret
    Scope https://www.googleapis.com/auth/bigquery https://www.googleapis.com/auth/bigquery.insertdata https://www.googleapis.com/auth/cloud-platform https://www.googleapis.com/auth/cloud-platform.read-only https://www.googleapis.com/auth/devstorage.full_control https://www.googleapis.com/auth/devstorage.read_only https://www.googleapis.com/auth/devstorage.read_write
    RetryMode RetryWhenStatusCodeMatch
    RetryStatusCodeList 429|503
    RetryCountMax 5
    RetryMultiplyWaitTime True
    Job Location
    Redirect URL (Only for Web App)
    ZappySys OAuth Connection

     Service Account [OAuth]

    Service accounts are accounts that do not represent a human user. They provide a way to manage authentication and authorization when a human is not directly involved, such as when an application needs to access Google Cloud resources. Service accounts are managed by IAM. [API reference]

    Steps how to get and use Google BigQuery credentials

    Follow these steps on how to create Service Account to authenticate and access BigQuery API in SSIS package or ODBC data source:

    Step-1: Create project

    This step is optional, if you already have a project in Google Cloud and can use it. However, if you don't, proceed with these simple steps to create one:

    1. First of all, go to Google API Console.

    2. Then click Select a project button and then click NEW PROJECT button:

      Start creating a new project in Google Cloud

    3. Name your project and click CREATE button:

      Create a new project in Google Cloud

    4. Wait until the project is created:

      Wait until project is created in Google Cloud
    5. Done! Let's proceed to the next step.

    Step-2: Enable Google Cloud APIs

    In this step we will enable BigQuery API and Cloud Resource Manager API:

    1. Select your project on the top bar:

      Select project in Google Cloud

    2. Then click the "hamburger" icon on the top left and access APIs & Services:

      Access APIs and services in Google Cloud

    3. Now let's enable several APIs by clicking ENABLE APIS AND SERVICES button:

      Enable API for project in Google Cloud

    4. In the search bar search for bigquery api and then locate and select BigQuery API:

      Search for API in Google Cloud

    5. If BigQuery API is not enabled, enable it:

      Enable Google BigQuery API

    6. Then repeat the step and enable Cloud Resource Manager API as well:

      Enable Cloud Resource Manager API
    7. Done! Let's proceed to the next step and create a service account.

    Step-3: Create Service Account

    Use the steps below to create a Service Account in Google Cloud:

    1. First of all, go to IAM & Admin in Google Cloud console:

      Access IAM & Admin in Google Cloud

    2. Once you do that, click Service Accounts on the left side and click CREATE SERVICE ACCOUNT button:

      Start creating service account in Google Cloud

    3. Then name your service account and click CREATE AND CONTINUE button:

      Create service account in Google Cloud

    4. Continue by clicking Select a role dropdown and start granting service account BigQuery Admin and Project Viewer roles:

      Start granting service account project roles in Google Cloud

    5. Find BigQuery group on the left and then click on BigQuery Admin role on the right:

      Grant service account BigQuery Admin role

    6. Then click ADD ANOTHER ROLE button, find Project group and select Viewer role:

      Grant service account project viewer role

    7. Finish adding roles by clicking CONTINUE button:

      Finish granting service account project roles in Google Cloud
      You can always add or modify permissions later in IAM & Admin.

    8. Finally, in the last step, just click button DONE:

      Finish configuring service account in Google Cloud

    9. Done! We are ready to add a Key to this service account in the next step.

    Step-4: Add Key to Service Account

    We are ready to add a Key (P12 certificate) to the created Service Account:

    1. In Service Accounts open newly created service account:

      Open service account in Google Cloud

    2. Next, copy email address of your service account for the later step:

      Copy service account email address in Google Cloud

    3. Continue by selecting KEYS tab, then press ADD KEY dropdown, and click Create new key menu item:

      Start creating key for service account in Google Cloud

    4. Finally, select P12 option and hit CREATE button:

      Create P12 key for service account in Google Cloud
    5. P12 certificate downloads into your machine. We have all the data needed for authentication, let's proceed to the last step!

    Step-5: Configure connection

    1. Now go to SSIS package or ODBC data source and configure these fields in Service Account authentication configuration:

      • In the Service Account Email field paste the service account Email address value you copied in the previous step.
      • In the Service Account Private Key Path (i.e. *.p12) field use downloaded certificate's file path.
    2. Done! Now you are ready to use Google BigQuery Connector!

    Configuring authentication parameters
    Google BigQuery
    Service Account [OAuth]
    https://www.googleapis.com/bigquery/v2
    Required Parameters
    Service Account Email Fill-in the parameter...
    Service Account Private Key Path (i.e. *.p12) Fill-in the parameter...
    ProjectId Fill-in the parameter...
    DatasetId (Choose after ProjectId) Fill-in the parameter...
    Optional Parameters
    Scope https://www.googleapis.com/auth/bigquery https://www.googleapis.com/auth/bigquery.insertdata https://www.googleapis.com/auth/cloud-platform https://www.googleapis.com/auth/cloud-platform.read-only https://www.googleapis.com/auth/devstorage.full_control https://www.googleapis.com/auth/devstorage.read_only https://www.googleapis.com/auth/devstorage.read_write
    RetryMode RetryWhenStatusCodeMatch
    RetryStatusCodeList 429
    RetryCountMax 5
    RetryMultiplyWaitTime True
    Job Location
    Impersonate As (Enter Email Id)
    ZappySys OAuth Connection

  9. Select the desired endpoint, change/pass the properties values, and click on Preview Data button to make the API call.

    API Source - Google BigQuery
    Read / write Google BigQuery data inside your app without coding using easy to use high performance API Connector
    API Source - Select Endpoint

  10. That's it! We are done! Just in a few clicks we configured the call to Google BigQuery using Google BigQuery 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)

    Execute Package - Reading data from Google BigQuery and load into target

Write data to Google BigQuery using SSIS (Import data)

In this section we will learn how to configure and use Google BigQuery Connector in the API Destination to write data to Google BigQuery.

Video tutorial

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 BigQuery integration in SSIS
  • How to configure connection for Google BigQuery
  • How to write or lookup data to Google BigQuery
  • Features about SSIS API Destination
  • Using Google BigQuery Connector in SSIS

Step-by-step instructions

In upper section we learned how to read data, now in this section we will learn how to configure Google BigQuery in the API Source to POST data to the Google BigQuery.

  1. Read the data from the source, being any desired source component. In example we will use ZappySys Dummy Data Source component.

  2. 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.
    SSIS API Destination (Predefined Templates) - Drag and Drop

  3. Select New Connection to create a new connection:

    API Destination - Google BigQuery
    Read / write Google BigQuery data inside your app without coding using easy to use high performance API Connector
    API Destination - New Connection

  4. Use a preinstalled Google BigQuery Connector from Popular Connector List or press Search Online radio button to download Google BigQuery Connector. Once downloaded simply use it in the configuration:

    Google BigQuery
    Google BigQuery Connector Selection

  5. 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.

     User Account [OAuth]

    User accounts represent a developer, administrator, or any other person who interacts with Google APIs and services. User accounts are managed as Google Accounts, either with Google Workspace or Cloud Identity. They can also be user accounts that are managed by a third-party identity provider and federated with Workforce Identity Federation. [API reference]

    Steps how to get and use Google BigQuery credentials

    Follow these steps on how to create Client Credentials (User Account principle) to authenticate and access BigQuery API in SSIS package or ODBC data source:

    WARNING: If you are planning to automate processes, we recommend that you use a Service Account authentication method. In case, you still need to use User Account, then make sure you use a system/generic account (e.g. automation@my-company.com). When you use a personal account which is tied to a specific employee profile and that employee leaves the company, the token may become invalid and any automated processes using that token will start to fail.

    Step-1: Create project

    This step is optional, if you already have a project in Google Cloud and can use it. However, if you don't, proceed with these simple steps to create one:

    1. First of all, go to Google API Console.

    2. Then click Select a project button and then click NEW PROJECT button:

      Start creating a new project in Google Cloud

    3. Name your project and click CREATE button:

      Create a new project in Google Cloud

    4. Wait until the project is created:

      Wait until project is created in Google Cloud
    5. Done! Let's proceed to the next step.

    Step-2: Enable Google Cloud APIs

    In this step we will enable BigQuery API and Cloud Resource Manager API:

    1. Select your project on the top bar:

      Select project in Google Cloud

    2. Then click the "hamburger" icon on the top left and access APIs & Services:

      Access APIs and services in Google Cloud

    3. Now let's enable several APIs by clicking ENABLE APIS AND SERVICES button:

      Enable API for project in Google Cloud

    4. In the search bar search for bigquery api and then locate and select BigQuery API:

      Search for API in Google Cloud

    5. If BigQuery API is not enabled, enable it:

      Enable Google BigQuery API

    6. Then repeat the step and enable Cloud Resource Manager API as well:

      Enable Cloud Resource Manager API
    7. Done! Let's proceed to the next step.

    Step-3: Create OAuth application

    1. First of all, click the "hamburger" icon on the top left and then hit VIEW ALL PRODUCTS:

      View all products in Google Cloud

    2. Then access Google Auth Platform to start creating an OAuth application:

      Open Google Auth Platform in Google Cloud

    3. Start by pressing GET STARTED button:

      Start creating an app in Google Cloud

    4. Next, continue by filling in App name and User support email fields:

      Fill app info in Google Cloud

    5. Choose Internal option, if it's enabled, otherwise select External:

      Choose app audience in Google Cloud

    6. Optional step if you used Internal option in the previous step. Nevertheless, if you had to use External option, then click ADD USERS to add a user:

      Add test user in Google Cloud app

    7. Then add your contact Email address:

      Enter app contact info in Google Cloud

    8. Finally, check the checkbox and click CREATE button:

      Create app in Google Cloud
    9. Done! Let's create Client Credentials in the next step.

    Step-4: Create Client Credentials

    1. In Google Auth Platform, select Clients menu item and click CREATE CLIENT button:

      Start creating app client in Google Cloud

    2. Choose Desktop app as Application type and name your credentials:

      Create OAuth app client in Google Cloud

    3. Continue by opening the created credentials:

      View app client credentials in Google Cloud

    4. Finally, copy Client ID and Client secret for the later step:

      Use client ID and secret to read Google REST API data

    5. Done! We have all the data needed for authentication, let's proceed to the last step!

    Step-5: Configure connection

    1. Now go to SSIS package or ODBC data source and use previously copied values in User Account authentication configuration:

      • In the ClientId field paste the Client ID value.
      • In the ClientSecret field paste the Client secret value.

    2. Press Generate Token button to generate Access and Refresh Tokens.

    3. Then choose ProjectId from the drop down menu.

    4. Continue by choosing DatasetId from the drop down menu.

    5. Finally, click Test Connection to confirm the connection is working.

    6. Done! Now you are ready to use Google BigQuery Connector!


    Configuring authentication parameters
    Google BigQuery
    User Account [OAuth]
    https://www.googleapis.com/bigquery/v2
    Required Parameters
    UseCustomApp Fill-in the parameter...
    ProjectId (Choose after [Generate Token] clicked) Fill-in the parameter...
    DatasetId (Choose after [Generate Token] clicked and ProjectId selected) Fill-in the parameter...
    Optional Parameters
    ClientId
    ClientSecret
    Scope https://www.googleapis.com/auth/bigquery https://www.googleapis.com/auth/bigquery.insertdata https://www.googleapis.com/auth/cloud-platform https://www.googleapis.com/auth/cloud-platform.read-only https://www.googleapis.com/auth/devstorage.full_control https://www.googleapis.com/auth/devstorage.read_only https://www.googleapis.com/auth/devstorage.read_write
    RetryMode RetryWhenStatusCodeMatch
    RetryStatusCodeList 429|503
    RetryCountMax 5
    RetryMultiplyWaitTime True
    Job Location
    Redirect URL (Only for Web App)
    ZappySys OAuth Connection

     Service Account [OAuth]

    Service accounts are accounts that do not represent a human user. They provide a way to manage authentication and authorization when a human is not directly involved, such as when an application needs to access Google Cloud resources. Service accounts are managed by IAM. [API reference]

    Steps how to get and use Google BigQuery credentials

    Follow these steps on how to create Service Account to authenticate and access BigQuery API in SSIS package or ODBC data source:

    Step-1: Create project

    This step is optional, if you already have a project in Google Cloud and can use it. However, if you don't, proceed with these simple steps to create one:

    1. First of all, go to Google API Console.

    2. Then click Select a project button and then click NEW PROJECT button:

      Start creating a new project in Google Cloud

    3. Name your project and click CREATE button:

      Create a new project in Google Cloud

    4. Wait until the project is created:

      Wait until project is created in Google Cloud
    5. Done! Let's proceed to the next step.

    Step-2: Enable Google Cloud APIs

    In this step we will enable BigQuery API and Cloud Resource Manager API:

    1. Select your project on the top bar:

      Select project in Google Cloud

    2. Then click the "hamburger" icon on the top left and access APIs & Services:

      Access APIs and services in Google Cloud

    3. Now let's enable several APIs by clicking ENABLE APIS AND SERVICES button:

      Enable API for project in Google Cloud

    4. In the search bar search for bigquery api and then locate and select BigQuery API:

      Search for API in Google Cloud

    5. If BigQuery API is not enabled, enable it:

      Enable Google BigQuery API

    6. Then repeat the step and enable Cloud Resource Manager API as well:

      Enable Cloud Resource Manager API
    7. Done! Let's proceed to the next step and create a service account.

    Step-3: Create Service Account

    Use the steps below to create a Service Account in Google Cloud:

    1. First of all, go to IAM & Admin in Google Cloud console:

      Access IAM & Admin in Google Cloud

    2. Once you do that, click Service Accounts on the left side and click CREATE SERVICE ACCOUNT button:

      Start creating service account in Google Cloud

    3. Then name your service account and click CREATE AND CONTINUE button:

      Create service account in Google Cloud

    4. Continue by clicking Select a role dropdown and start granting service account BigQuery Admin and Project Viewer roles:

      Start granting service account project roles in Google Cloud

    5. Find BigQuery group on the left and then click on BigQuery Admin role on the right:

      Grant service account BigQuery Admin role

    6. Then click ADD ANOTHER ROLE button, find Project group and select Viewer role:

      Grant service account project viewer role

    7. Finish adding roles by clicking CONTINUE button:

      Finish granting service account project roles in Google Cloud
      You can always add or modify permissions later in IAM & Admin.

    8. Finally, in the last step, just click button DONE:

      Finish configuring service account in Google Cloud

    9. Done! We are ready to add a Key to this service account in the next step.

    Step-4: Add Key to Service Account

    We are ready to add a Key (P12 certificate) to the created Service Account:

    1. In Service Accounts open newly created service account:

      Open service account in Google Cloud

    2. Next, copy email address of your service account for the later step:

      Copy service account email address in Google Cloud

    3. Continue by selecting KEYS tab, then press ADD KEY dropdown, and click Create new key menu item:

      Start creating key for service account in Google Cloud

    4. Finally, select P12 option and hit CREATE button:

      Create P12 key for service account in Google Cloud
    5. P12 certificate downloads into your machine. We have all the data needed for authentication, let's proceed to the last step!

    Step-5: Configure connection

    1. Now go to SSIS package or ODBC data source and configure these fields in Service Account authentication configuration:

      • In the Service Account Email field paste the service account Email address value you copied in the previous step.
      • In the Service Account Private Key Path (i.e. *.p12) field use downloaded certificate's file path.
    2. Done! Now you are ready to use Google BigQuery Connector!

    Configuring authentication parameters
    Google BigQuery
    Service Account [OAuth]
    https://www.googleapis.com/bigquery/v2
    Required Parameters
    Service Account Email Fill-in the parameter...
    Service Account Private Key Path (i.e. *.p12) Fill-in the parameter...
    ProjectId Fill-in the parameter...
    DatasetId (Choose after ProjectId) Fill-in the parameter...
    Optional Parameters
    Scope https://www.googleapis.com/auth/bigquery https://www.googleapis.com/auth/bigquery.insertdata https://www.googleapis.com/auth/cloud-platform https://www.googleapis.com/auth/cloud-platform.read-only https://www.googleapis.com/auth/devstorage.full_control https://www.googleapis.com/auth/devstorage.read_only https://www.googleapis.com/auth/devstorage.read_write
    RetryMode RetryWhenStatusCodeMatch
    RetryStatusCodeList 429
    RetryCountMax 5
    RetryMultiplyWaitTime True
    Job Location
    Impersonate As (Enter Email Id)
    ZappySys OAuth Connection

  6. Select the desired endpoint, change/pass the properties values, and go to the Mappings tab to map the columns.

    API Destination - Google BigQuery
    Read / write Google BigQuery data inside your app without coding using easy to use high performance API Connector
    API Destination - Select Endpoint

  7. Finally, map the desired columns:

    API Destination - Google BigQuery
    Read / write Google BigQuery data inside your app without coding using easy to use high performance API Connector
    API Destination - Columns Mapping

  8. That's it; we successfully configured the POST API Call. In a few clicks we configured the Google BigQuery API call using ZappySys Google BigQuery Connector

    Execute Package - Reading data from API Source and load into target

Load Google BigQuery data into SQL Server using Upsert Destination (Insert or Update)

Once you read data from the desired source, now let's see how to Load Google BigQuery 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.

  1. From the SSIS toolbox drag and drop Upsert Destination on the dataflow designer surface

  2. Connect our Source component to Upsert Destination

  3. Double click on Upsert Destination component to configure it.

  4. 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
    Configure SSIS Upsert Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS

  5. 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.
    SSIS SQL Upsert option

  6. Click on OK to save Upsert Destination settings UI.

  7. That's it. Run the SSIS Package and it will read the data from the Google BigQuery and load the data in the SQL Server/PostgreSql/Redshift using Upsert Destination.

    Execute Package - Reading data from API Source and load into target

Deploy and schedule SSIS package

After you are done creating SSIS package, most likely, you want to deploy it to SQL Server Catalog and run it periodically. Just follow the instructions in How to design, debug, deploy, schedule SSIS Package (In SQL Agent and Catalog) article to see how to do it.

Advanced topics

Actions supported by Google BigQuery Connector

Google BigQuery 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.
 Read Data using SQL Query -OR- Execute Script (i.e. CREATE, SELECT, INSERT, UPDATE, DELETE)
Runs a BigQuery SQL query synchronously and returns query results if the query completes within a specified timeout    [Read more...]
Parameter Description
SQL Statement (i.e. SELECT / DROP / CREATE)
Option Value
Example1 SELECT title,id,language,wp_namespace,reversion_id ,comment,num_characters FROM bigquery-public-data.samples.wikipedia LIMIT 1000
Example2 CREATE TABLE TestDataset.Table1 (ID INT64,Name STRING,BirthDate DATETIME, Active BOOL)
Example3 INSERT TestDataset.Table1 (ID, Name,BirthDate,Active) VALUES(1,'AA','2020-01-01',true),(2,'BB','2020-01-02',true),(3,'CC','2020-01-03',false)
Use Legacy SQL Syntax?
Option Value
false false
true true
timeout (Milliseconds) Wait until timeout is reached.
Option Value
false false
true true
Job Location The geographic location where the job should run. For Non-EU and Non-US datacenters we suggest you to supply this parameter to avoid any error.
Option Value
System Default
Data centers in the United States US
Data centers in the European Union EU
Columbus, Ohio us-east5
Iowa us-central1
Las Vegas us-west4
Los Angeles us-west2
Montréal northamerica-northeast1
Northern Virginia us-east4
Oregon us-west1
Salt Lake City us-west3
São Paulo southamerica-east1
Santiago southamerica-west1
South Carolina us-east1
Toronto northamerica-northeast2
Delhi asia-south2
Hong Kong asia-east2
Jakarta asia-southeast2
Melbourne australia-southeast2
Mumbai asia-south1
Osaka asia-northeast2
Seoul asia-northeast3
Singapore asia-southeast1
Sydney australia-southeast1
Taiwan asia-east1
Tokyo asia-northeast1
Belgium europe-west1
Finland europe-north1
Frankfurt europe-west3
London europe-west2
Madrid europe-southwest1
Milan europe-west8
Netherlands europe-west4
Paris europe-west9
Warsaw europe-central2
Zürich europe-west6
AWS - US East (N. Virginia) aws-us-east-1
Azure - East US 2 azure-eastus2
Custom Name (Type your own) type-region-id-here
 Read Table Rows
Gets the specified table resource by table ID. This method does not return the data in the table, it only returns the table resource, which describes the structure of this table.    [Read more...]
Parameter Description
ProjectId Leave this value blank to use ProjectId from connection settings
DatasetId Leave this value blank to use DatasetId from connection settings
TableId
 [$parent.tableReference.datasetId$].[$parent.tableReference.tableId$]
Read data from [$parent.tableReference.datasetId$].[$parent.tableReference.tableId$] for project .    [Read more...]
Parameter Description
 List Projects
Lists Projects that the caller has permission on and satisfy the specified filter.    [Read more...]
Parameter Description
SearchFilter An expression for filtering the results of the request. Filter rules are case insensitive. If multiple fields are included in a filter query, the query will return results that match any of the fields. Some eligible fields for filtering are: name, id, labels.{key} (where key is the name of a label), parent.type, parent.id, lifecycleState. Example: name:how*
 List Datasets
Lists all BigQuery datasets in the specified project to which the user has been granted the READER dataset role.    [Read more...]
Parameter Description
ProjectId
SearchFilter An expression for filtering the results of the request. Filter rules are case insensitive. If multiple fields are included in a filter query, the query will return results that match any of the fields. Some eligible fields for filtering are: name, id, labels.{key} (where key is the name of a label), parent.type, parent.id, lifecycleState. Example: name:how*
all Whether to list all datasets, including hidden ones
Option Value
True True
False False
 Create Dataset
Creates a new empty dataset.    [Read more...]
Parameter Description
ProjectId
Dataset Name Enter dataset name
Description
 Delete Dataset
Deletes the dataset specified by the datasetId value. Before you can delete a dataset, you must delete all its tables, either manually or by specifying deleteContents. Immediately after deletion, you can create another dataset with the same name.    [Read more...]
Parameter Description
ProjectId
DatasetId
Delete All Tables If True, delete all the tables in the dataset. If False and the dataset contains tables, the request will fail. Default is False
Option Value
True True
False False
 Delete Table
Deletes the dataset specified by the datasetId value. Before you can delete a dataset, you must delete all its tables, either manually or by specifying deleteContents. Immediately after deletion, you can create another dataset with the same name.    [Read more...]
Parameter Description
ProjectId
DatasetId
TableId
 List Tables
Lists BigQuery Tables for the specified project / dataset to which the user has been granted the READER dataset role.    [Read more...]
Parameter Description
ProjectId
DatasetId
 Get Query Schema (From SQL)
Runs a BigQuery SQL query synchronously and returns query schema    [Read more...]
Parameter Description
SQL Query
Filter
Use Legacy SQL Syntax?
Option Value
false false
true true
timeout (Milliseconds) Wait until timeout is reached.
Option Value
false false
true true
Job Location The geographic location where the job should run. For Non-EU and Non-US datacenters we suggest you to supply this parameter to avoid any error.
Option Value
System Default
Data centers in the United States US
Data centers in the European Union EU
Columbus, Ohio us-east5
Iowa us-central1
Las Vegas us-west4
Los Angeles us-west2
Montréal northamerica-northeast1
Northern Virginia us-east4
Oregon us-west1
Salt Lake City us-west3
São Paulo southamerica-east1
Santiago southamerica-west1
South Carolina us-east1
Toronto northamerica-northeast2
Delhi asia-south2
Hong Kong asia-east2
Jakarta asia-southeast2
Melbourne australia-southeast2
Mumbai asia-south1
Osaka asia-northeast2
Seoul asia-northeast3
Singapore asia-southeast1
Sydney australia-southeast1
Taiwan asia-east1
Tokyo asia-northeast1
Belgium europe-west1
Finland europe-north1
Frankfurt europe-west3
London europe-west2
Madrid europe-southwest1
Milan europe-west8
Netherlands europe-west4
Paris europe-west9
Warsaw europe-central2
Zürich europe-west6
AWS - US East (N. Virginia) aws-us-east-1
Azure - East US 2 azure-eastus2
Custom Name (Type your own) type-region-id-here
 Get Table Schema
Gets the specified table resource by table ID. This method does not return the data in the table, it only returns the table resource, which describes the structure of this table.    [Read more...]
Parameter Description
DatasetId
TableId
Filter
 insert_table_data
   [Read more...]
Parameter Description
ProjectId
DatasetId
TableId
 post_[$parent.tableReference.datasetId$]_[$parent.tableReference.tableId$]
   [Read more...]
 Generic Request
This is generic endpoint. Use this endpoint when some actions are not implemented by connector. Just enter partial URL (Required), Body, Method, Header etc. Most parameters are optional except URL.    [Read more...]
Parameter Description
Url API URL goes here. You can enter full URL or Partial URL relative to Base URL. If it is full URL then domain name must be part of ServiceURL or part of TrustedDomains
Body Request Body content goes here
IsMultiPart Set this option if you want to upload file(s) (i.e. POST RAW file data) or send data using Multi-Part encoding method (i.e. Content-Type: multipart/form-data). Multi-Part request allows you to mix key/value and upload files in same request. On the other hand raw upload allows only single file upload (without any key/value) ==== Raw Upload (Content-Type: application/octet-stream) ===== To upload single file in raw mode check this option and specify full file path starting with @ sign in the Body (e.g. @c:\data\myfile.zip ) ==== Form-Data / Multipart Upload (Content-Type: multipart/form-data) ===== To treat your Request data as multi part fields you must specify key/value pairs separated by new lines into RequestData field (i.e. Body). Each key value pair is entered on new-line and key/value are separated using equal sign (=). Preceding and trailing spaces are ignored also blank lines are ignored. If field value has some any special character(s) then use escape sequence (e.g. For NewLine: \r\n, For Tab: \t, For at (@): \@). When value of any field starts with at sign (@) its automatically treated as File you want to upload. By default file content type is determined based on extension however you can supply content type manually for any field using this way [ YourFileFieldName.Content-Type=some-content-type ]. By default File Upload Field always includes Content-Type in the request (non file fields do not have content-type by default unless you supply manually). For some reason if you dont want to use Content-Type header in your request then supply blank Content-Type to exclude this header altogather [e.g. SomeFieldName.Content-Type= ]. In below example we have supplied Content-Type for file2 and SomeField1, all other fields are using default content-type. See below Example of uploading multiple files along with additional fields. If some API requires you to pass Content-Type: multipart/form-data rather than multipart/form-data then manually set Request Header => Content-Type: multipart/mixed (it must starts with multipart/ else will be ignored). file1=@c:\data\Myfile1.txt file2=@c:\data\Myfile2.json file2.Content-Type=application/json SomeField1=aaaaaaa SomeField1.Content-Type=text/plain SomeField2=12345 SomeFieldWithNewLineAndTab=This is line1\r\nThis is line2\r\nThis is \ttab \ttab \ttab SomeFieldStartingWithAtSign=\@MyTwitterHandle
Filter Enter filter to extract array from response. Example: $.rows[*] --OR-- $.customers[*].orders[*]. Check your response document and find out hierarchy you like to extract
Option Value
No filter
Example1 $.store.books[*]
Example2 (Sections Under Books) $.store.books[*].sections[*]
Example3 (Equals) $.store.books[?(@author=='sam')]
Example4 (Equals - Any Section) $..[?(@author=='sam')]
Example5 (Not Equals - Any Section) $..[?(@author!='sam')]
Example6 (Number less than) $.store.books[?(@.price<10)] Example7 (Regular Expression - Contains Pattern)=$.store.books[?(@author=~ /sam|bob/ )]
Example8 (Regular Expression - Does Not Contain Pattern) $.store.books[?(@author=~ /^((?!sam|bob).)*$/ )]
Example9 (Regular Expression - Exact Pattern Match) $.store.books[?(@author=~ /^sam|bob$/ )]
Example10 (Regular Expression - Starts With) $.store.books[?(@author=~ /^sam/ )]
Example11 (Regular Expression - Ends With) $.store.books[?(@author=~ /sam$/ )]
Example12 (Between) $.store.employees[?( @.hiredate>'2015-01-01' && @.hiredate<'2015-01-04' )]
Headers Headers for Request. To enter multiple headers use double pipe or new line after each {header-name}:{value} pair
 Generic Request (Bulk Write)
This is a generic endpoint for bulk write purpose. Use this endpoint when some actions are not implemented by connector. Just enter partial URL (Required), Body, Method, Header etc. Most parameters are optional except URL.    [Read more...]
Parameter Description
Url API URL goes here. You can enter full URL or Partial URL relative to Base URL. If it is full URL then domain name must be part of ServiceURL or part of TrustedDomains
IsMultiPart Set this option if you want to upload file(s) (i.e. POST RAW file data) or send data using Multi-Part encoding method (i.e. Content-Type: multipart/form-data). Multi-Part request allows you to mix key/value and upload files in same request. On the other hand raw upload allows only single file upload (without any key/value) ==== Raw Upload (Content-Type: application/octet-stream) ===== To upload single file in raw mode check this option and specify full file path starting with @ sign in the Body (e.g. @c:\data\myfile.zip ) ==== Form-Data / Multipart Upload (Content-Type: multipart/form-data) ===== To treat your Request data as multi part fields you must specify key/value pairs separated by new lines into RequestData field (i.e. Body). Each key value pair is entered on new-line and key/value are separated using equal sign (=). Preceding and trailing spaces are ignored also blank lines are ignored. If field value has some any special character(s) then use escape sequence (e.g. For NewLine: \r\n, For Tab: \t, For at (@): \@). When value of any field starts with at sign (@) its automatically treated as File you want to upload. By default file content type is determined based on extension however you can supply content type manually for any field using this way [ YourFileFieldName.Content-Type=some-content-type ]. By default File Upload Field always includes Content-Type in the request (non file fields do not have content-type by default unless you supply manually). For some reason if you dont want to use Content-Type header in your request then supply blank Content-Type to exclude this header altogather [e.g. SomeFieldName.Content-Type= ]. In below example we have supplied Content-Type for file2 and SomeField1, all other fields are using default content-type. See below Example of uploading multiple files along with additional fields. If some API requires you to pass Content-Type: multipart/form-data rather than multipart/form-data then manually set Request Header => Content-Type: multipart/mixed (it must starts with multipart/ else will be ignored). file1=@c:\data\Myfile1.txt file2=@c:\data\Myfile2.json file2.Content-Type=application/json SomeField1=aaaaaaa SomeField1.Content-Type=text/plain SomeField2=12345 SomeFieldWithNewLineAndTab=This is line1\r\nThis is line2\r\nThis is \ttab \ttab \ttab SomeFieldStartingWithAtSign=\@MyTwitterHandle
Filter Enter filter to extract array from response. Example: $.rows[*] --OR-- $.customers[*].orders[*]. Check your response document and find out hierarchy you like to extract
Headers Headers for Request. To enter multiple headers use double pipe (||) or new line after each {header-name}:{value} pair

Conclusion

In this article we discussed how to connect to Google BigQuery in SSIS and integrate data without any coding. Click here to Download Google BigQuery 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 BigQuery Connector for SSIS Documentation 

More integrations

Other application integration scenarios for Google BigQuery

Google BigQuery for SQL Server
SQL Server
Google BigQuery for Power BI
Power BI
Google BigQuery for SSRS
SSRS
Google BigQuery for Informatica
Informatica
Google BigQuery for MS Access
MS Access
Google BigQuery for MS Excel
MS Excel
Google BigQuery for SSAS
SSAS
Google BigQuery for C#
C#
Google BigQuery for Python
Python
Google BigQuery for JAVA
JAVA
Google BigQuery for Tableau
Tableau
Google BigQuery for SAP Crystal Reports
SAP Crystal Reports
Google BigQuery for Azure Data Factory (Pipeline)
Azure Data Factory (Pipeline)
Google BigQuery for Azure Data Factory (SSIS)
Azure Data Factory (SSIS)
Google BigQuery for Talend Studio
Talend Studio
Google BigQuery for UiPath
UiPath
Google BigQuery for PowerShell
PowerShell
Google BigQuery for ODBC
ODBC

Other connectors for SSIS

Amazon Ads Connector
Amazon Ads
Amazon Selling Partner (SP-API) Connector
Amazon Selling Partner (SP-API)
Asana Connector
Asana
Azure DevOps Connector
Azure DevOps
Cosmos DB Connector
Cosmos DB
DropBox Connector
DropBox
ElasticSearch Connector
ElasticSearch
FastSpring Connector
FastSpring
Google BigQuery Connector
Google BigQuery
Google Calendar Connector
Google Calendar
Google Drive Connector
Google Drive
Google Sheets Connector
Google Sheets
Hubspot Connector
Hubspot
Jira Connector
Jira
Mailchimp Connector
Mailchimp
ManagedEngine ServiceDesk Plus (Zoho) Connector
ManagedEngine ServiceDesk Plus (Zoho)
OData Connector
OData
OneDrive Connector
OneDrive
Outlook Mail (Office 365) Connector
Outlook Mail (Office 365)
Power BI Connector
Power BI
ServiceNow Connector
ServiceNow
SharePoint Online Connector
SharePoint Online
Shopify Connector
Shopify
Smartsheet Connector
Smartsheet
Stripe Connector
Stripe
YouTube Connector
YouTube
Zendesk Connector
Zendesk
Zoho CRM Connector
Zoho CRM
Zoho SalesIQ Connector
Zoho SalesIQ
Zoom Connector
Zoom
REST API Connector
REST API
JSON Connector
JSON
JSON File Connector
JSON File
XML Connector
XML
SOAP Connector
SOAP
XML File Connector
XML File
JDBC-ODBC Bridge Connector
JDBC-ODBC Bridge
Salesforce Connector
Salesforce
CSV Connector
CSV
Amazon S3 JSON File Connector
Amazon S3 JSON File
Amazon S3 XML File Connector
Amazon S3 XML File
Amazon S3 CSV File Connector
Amazon S3 CSV File
Azure Blob JSON File Connector
Azure Blob JSON File
Azure Blob XML File Connector
Azure Blob XML File
Azure Blob CSV File Connector
Azure Blob CSV File
FTP/SFTP JSON File Connector
FTP/SFTP JSON File
FTP/SFTP XML File Connector
FTP/SFTP XML File
FTP/SFTP CSV File Connector
FTP/SFTP CSV File
Custom Connector
Build your own Custom API Connector


Download Google BigQuery Connector for SSIS Documentation

  • How to connect Google BigQuery in SSIS?

  • How to get Google BigQuery data in SSIS?

  • How to read Google BigQuery data in SSIS?

  • How to load Google BigQuery data in SSIS?

  • How to import Google BigQuery data in SSIS?

  • How to pull Google BigQuery data in SSIS?

  • How to push data to Google BigQuery in SSIS?

  • How to write data to Google BigQuery in SSIS?

  • How to POST data to Google BigQuery in SSIS?

  • Call Google BigQuery API in SSIS

  • Consume Google BigQuery API in SSIS

  • Google BigQuery SSIS Automate

  • Google BigQuery SSIS Integration

  • Integration Google BigQuery in SSIS

  • Consume real-time Google BigQuery data in SSIS

  • Consume real-time Google BigQuery API data in SSIS

  • Google BigQuery ODBC Driver | ODBC Driver for Google BigQuery | ODBC Google BigQuery Driver | SSIS Google BigQuery Source | SSIS Google BigQuery Destination

  • Connect Google BigQuery in SSIS

  • Load Google BigQuery in SSIS

  • Load Google BigQuery data in SSIS

  • Read Google BigQuery data in SSIS

  • Google BigQuery API Call in SSIS