Help Manual - Google BigQuery Connection Manager

Documentation on SSIS Google BigQuery Connection Manager component

Using the Google BigQuery Command Task

The Google BigQuery Command Task is a control flow component that can be used to run commands to Google BigQuery directly at the control flow level. It can be used to execute queries, and also to get the output in a required manner.

There are three pages that can be configured.

  • General
  • Output
  • Error Handling

General Page

The General page includes the general properties for the component.

Google BigQuery Command Task - General.png

Connection Manager

The Connection Manager drop-down displays a list of all connection managers that are available for your current SSIS package.

Billing Project(since v22.1)

The Billing Project option allows you to specify which billing project is for executing jobs.

Project

The Project option allows you to specify which project you want to read from Google BigQuery. The drop-down will present a list of all available projects in your Google BigQuery.

Dataset

The Dataset option allows you to specify which dataset you want to read from Google BigQuery. The drop-down will present a list of all available datasets in the specified project.

Page Size

The Page Size option allows you to specify how many records you want to retrieve each time.

Parameterize Query

Parameterizing query makes sure that SSIS variable values are parameterized instead of simply text-merged when they are applied to the query command. By default, this option is checked.

Create Query Job

The Create Query Job option allows you to create a query job in Big Query when executing BigQuery SQL query text.

Poll Throttle Rate

The Poll Throttle Rate option will limit the number of polling requests that can be sent per second. This is useful to limit the rates to get Query Job status when you enable the Create Query Job option.

Note: This option is only available when Create Query Job option is enabled.

Use Query Cache

The Use Query Cache option allows you to specify whether to look for the result in the query cache. This option is unchecked by default.

Use Legacy SQL

The Use Legacy SQL option allows you to specify whether to use Google BigQuery's legacy SQL dialect for this query. This option is checked by default. If you uncheck this option, the query will use Google BigQuery's standard SQL.

Command

The Command textbox is the command text that will be executed to perform an action against the connected Google BigQuery instance. You can easily create a command by dragging and dropping the table names and adding the keywords from the panel on the left-side to the command editor window.

The Command textbox supports the use of User and System Variables. Simply select a variable under the SSIS Variables menu in the left-hand side panel, and a placeholder value will be inserted into the filter text.

Import

Loads command from a file into the Command property.

Export

Save the command in the Command property to a file.

Preview

Opens a preview dialog that shows the result (up to the first 200 rows) of executing the text in the Command property over the specified Connection Manager.

Note: If the command makes any changes to the database, the changes will appear in the preview but are rolled back immediately. Changes to the database will only commit at runtime.

Expression fx Button

Click the fx button to launch SSIS Expression Editor to enable dynamic updates of the property at run time.

Outputs Page

The Output page of the Google BigQuery Command Task shows you the available output settings.

Google BigQuery Command Task - Output.png

Output Type

The Output Type can be any of the list below:

  • None
  • Single Row
  • Scalar
  • Result Set
Single Row Selection Mode (Only for Single Row Output Type)

You could choose the Single Row Selection mode from the below:

  • First
  • Last
  • Specify Row Index
Single Row Index (Only for Single Row Output Type)

Specify the Single Row index value in this field.

Single Row Output Variables (Only for Single Row Output Type)

The grid can be used to set the Column Index and the Output Variables

  • Column Index: The index for the column to be read.
  • Output variable: The variable to which it should be written
Scalar Output Variable (Only for Scalar Output Type)

Choose the variable to store the Scalar output.

Result Set Variable (Only for Result Set Output Type)

Choose the variable to store the result set output. Usually, this is an Object variable.

Error Handling Page

The Error Handling page can be used to handle errors.

Google BigQuery Command Task - Error handling.png

Fail on error

When chosen, will fail the component on error.

Write error to variable

Will write the error message to a variable.

Ignore error

Ignores the error and continues the task.