Extracting BigQuery Data into SSIS: A Comprehensive Guide


This article guides you through connecting to Google BigQuery API in SSIS / ODBC.



Create/Select a Project in Google API Console:

  1. Navigate to Google API Console.

  2. Click on the Project Dropdown at the top bar and either select an existing project or create a new one by clicking CREATE PROJECT.

  3. Once the project is set, click ENABLE APIS AND SERVICES.

  4. Enable both BigQuery Connection API and Cloud Resource Manager API by searching for them and clicking ENABLE.

  5. Return to the main screen and click on the OAuth Consent Screen tab. Provide the necessary details and save.

  6. Move to the Credentials tab.

  7. Click CREATE CREDENTIALS in the top bar, choose OAuth Client ID, select Desktop App as the Application Type, and click Create to obtain your Client ID and Secret.

Connect with Google BigQuery connector in SSIS:

  1. Drag and drop a Data Flow task in your SSIS package.

  2. Inside the Data Flow, add an API Source component.

  3. Create a new connection within the API Source, and choose the Google BigQuery connector. Press Continue for the next step.

  4. Fill in the connector fields, including Client ID, Client Secret ID, ProjectID, and DataSetID. After generating the token, you can choose an option with a dropdown in the last 2 fields.

  5. Generate the token and click OK to save the configuration.

  6. Select a table/endpoint and preview the data.


Save the credentials so you do not have to search in your account every time.


Following these steps, you can seamlessly connect to Google BigQuery within SSIS, utilizing the ZappySys tool for efficient data retrieval and integration. If you encounter any issues or need further assistance, you can contact our support team via chat on our website or through email at support@zappysys.com.


You can check our connector Google BigQuery article here.