ODBC TUTORIAL

Connect to Google BigQuery database from SSIS (SQL Server Integration Service) using the DataDirect Google BigQuery ODBC Driver

Updated: 31 Jul 2024

Introduction

The SQL Server Integration Service is an ETL (Extract Transform and Load) Tool from Microsoft for building enterprise-level data integration and transformation solutions.   

It can be used not only for basic data integration and workflow applications but also to perform broad data migration tasks. It features a data warehousing tool used for data extraction, transformation, and loading.  

 

Prerequisites

This tutorial assumes that you have ODBC drivers installed and a data source configured. Ensure that Visual Studio and ODBC Driver architecture matches. For example, if you have a 64-Bit Microsoft Visual Studio installed then you need to have the 64-Bit version of Progress DataDirect ODBC drivers installed. Most likely, your Visual Studio would be a 32-bit version and you would need a 32-bit driver. If you have not yet done these steps, please follow these instructions:     

 

  1. Download and install the Progress DataDirect Google BigQuery ODBC driver for your database using the 15-day trial software. In this tutorial, we are connecting to Google BigQuery.     
  2. Configure a data source for your database. See the DataDirect documentation for assistance with setting up your data source. The sample below uses a data source.  

 

Configure Connection to Google BigQuery ODBC Driver Using the ODBC Administrator

1. After the DataDirect Google BigQuery ODBC driver has been installed, open ODBC Administrator to configure the connection and click on “Add.”  

 

  

 

 

2. Choose the “DataDirect X.X Google BigQuery” driver and click on “Finish.”    

 

     

 

3. You should now see the ODBC Google BigQuery Driver Setup pop-up window launched.    

 

             

 

4. Add your Data Source Name, project, dataset, and other credentials, and click on “Apply” and then “Test Connect.”   

 

           

 

 

5. A pop-up window will confirm that the connection is established.    

 

                        

 

Configure the DataDirect ODBC Google BigQuery Driver with Microsoft Visual Studio

1. Open Microsoft Visual Studio. You need to create a new SSIS project by going to File → New → Project. 

 

Graphical user interface, application Description automatically generated 

 

2. A new window will appear, and inside that select Integration Service Project(If not appearing, go to Templates → Business Intelligence → Integration Services on the left side of the window).  

  

  • Specify a suitable project Name and choose the desired location using “Browse...” at the lower side of the window. 

Graphical user interface, text, application, email Description automatically generated 

 

  • Must ensure SSIS Toolbox is present on the left side of the window and Solution 'Integration Service Project' is present on the right side of the window.