Connect to Google BigQuery database from SSIS (SQL Server Integration Service) using the DataDirect Google BigQuery ODBC Driver
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:
- 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.
- 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.
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.
- 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.
- If the SSIS Toolbox does not appear on the left side of the window, then right-click on Control Flow Pageand select SSIS Toolbox.
3. From the left side of the window, under SSIS Toolbox → Favorites → Data Flow Task, drag and drop the Data Flow Taskinto Package.dtsx[Design]*Page section.
4. Double-click on the Data Flow Task, you must be automatically moved to Data Flow Pagefrom Control Flow Page, and now the Data Flow Pageis blank.
- Now, you need to choose/select your data source type from the left side of the window under SSIS Toolbox → Other Sources, Drag and Drop desired data source into the Data Flow Page.
- Now, you need to establish a connection from that desired data source. For that, below the Data Flow Page, under Connection ManageSection, right-click and select New Connectionfrom the appeared menu list.
5. A new window will appear in front of you, select the desired Connection Manager type and click on Add. Here we are connecting to Google BigQuery ODBC, so we add ODBC Connection Manager.
6. We are creating a new connection manager so click on “New” and then click on “OK.”
7. A new pop-up will appear. Select the “Use connection string” radial button and click on “Build.”
8. Go to Machine data source, select your Data Source, and click “OK.”
9. Give the required logon credentials and click on “OK.”
10. A connection string is built in the connection manager and now you can click on “OK.”
11. Connection Manager is created, and you can see your Data source in the data connections. Select it and click on “OK.”
12. After successfully connecting to the source DSN, you can preview the source data by clicking on Preview, and a popup will appear titled “Preview Query Results.”
13. Now, you need to choose/select the desired destination type from the left side of the window under SSIS Toolbox → Other Destinations.Drag and Drop desired data destination into the Data Flow Page, similar to “Data source.”
14. Now, you need to connect theODBC Source andODBC Destinationusing the mouse pointer. Move the mouse cursor over the ODBC Source. A blue arrow will appear, you need to expand it and connect to the ODBC Destination.
15. Similarly, you need to configure the destination too. Double-click on ODBC Destinationand a window will appear having the title ODBC Destination. Check the ODBC Connection Manager drop-down listto see if your desired connection is present or not. If not, click on New. The available ODBC connection will show, select it. Also, select the table and click “OK.”
16. After this, you need to map the columns between the ODBC Source Table to the ODBC Destination Table by clicking on the Mappings Tab, available on the left side of the window and just below the Connection Manager.
- Map the columns as one-to-one, as per requirement, and must ensure the data type of the mapping columns is the same.
17. Now click on “Start.” The data transfer process has started.
- You can see the progress by clicking on Progress.
18. Finally, the data is transferred from the ODBC Source table to the ODBC Destinationtable.
Conclusion
Get started today with a free 15-day trial of Progress DataDirect Google BigQuery ODBC driver and connect Microsoft SQL Server SSIS to all your data! For any help, please feel free to contact us.