Google BigQuery Destination Component

The Google BigQuery Destination Component is an SSIS data flow pipeline component that can be used to write data to Google BigQuery. There are three pages of configuration:

  • General
  • Columns
  • Error Handling

The General page is used to specify general settings for the Google BigQuery Destination component. The Columns page is used to manage columns from the upstream component. The Error Handling page allows you to specify how errors should be handled when they occur.

General page

The General page allows you to specify general settings for the component.

SSIS Google BigQuery Destination

Connection Manager

The Google BigQuery Destination Component requires a connection. The Connection Manager drop-down will show a list of connection managers that are available.

Object Type

The Object Type option allows you to specify the Google BigQuery Object you want to work with. There are four types available:

  • DATASET
  • JOB
  • TABLE
  • TABLEDATA
Action

Depending on the Object Type you have specified, there are different Actions available for different Object Type:

  • Insert: DATASET, JOB, TABLE and TABLEDATA
  • Update: DATASET and TABLE
  • Patch: DATASET and TABLE
  • Delete: DATASET and TABLE
  • Cancel: JOB
Delete Contents

The Delete Contents option specifies whether you want to delete all the tables in the dataset. If you leave this option unchecked and the dataset contains tables, the request will fail.

Note: This option is only available to DATASET object.

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.

Note: This option is only available to TABLEDATA object.

DataSet

The Data Set 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.

Note: This option is only available to TABLEDATA object.

Table

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

Note: This option is only available to TABLEDATA object.

Page Size

The Page Size option allows you to specify the how many records to send in a request.

Note: This option is only available to TABLEDATA object.

Template Suffix

If the Template Suffix option is specified, Google BigQuery will treat the destination table as a base template, and inserts the rows into an instance table named "{destination}{templateSuffix}". BigQuery will manage creation of the instance table, using the schema of the base template table. For more details, please check here for considerations when working with templates tables.

Note: This option is only available to TABLEDATA object.

Ignore Unknown Values

If the Ignore Unknown Values option is enabled, Google BigQuery will accept rows that contain values that do not match the schema. The unknown values are ignored. It is unchecked by default.

Note: This option is only available to TABLEDATA object.

Skip Invalid Rows

If the Skip Invalid Rows option is enabled, the component will insert all valid rows to the specified table, even if invalid rows exist. It is unchecked by default, which causes the entire insert action to fail if any invalid rows exist.

Note: This option is only available to TABLEDATA object.

Insert As JSON

The Insert As JSON option specifies whether the input should be one single input column which takes the value in JSON format from upstream component.

Note: This option is only available to TABLEDATA object.

Refresh Component Button

Clicking the Refresh Component Button will bring up a prompt for you to confirm the refresh. After clicking “Yes”, it will remove any existing columns and add all columns from upstream pipeline components.

Reset Columns Button

Clicking the Reset Columns button will bring up a prompt for you to confirm the reset. After clicking “Yes”, it will remove any existing columns and replace them with that of the Input Columns.

Map Unmapped Fields Button

By clicking this button, the component will try to map any unmapped attributes by matching their names with the input columns from upstream components.

Clear All Mappings Button

By clicking this button, the component will reset (clear) all your mappings in the destination component.

Expression fx Button

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

Generate Documentation Button

Clicking the Generate Documentation button to generate a Word document which describes the component's metadata including relevant mapping, and so on.

Columns page

The Columns page of the Google BigQuery Destination Component allows you map the columns from upstream components to the Google BigQuery Fields.

In the Columns page, you would see a grid that contains four columns as shown below.

SSIS Google BigQuery Destination - columns

  • Input Column: You can select an input column from an upstream component for the corresponding Google BigQuery Field.
  • Google BigQuery Field: The field that you are writing data to.
  • Data Type: This column indicates the type of value for the current field.
  • Unmap: This column can be used to unmap the field from the upstream input column, or otherwise it can be used to map the field to an upstream input column by matching its name if the field is not currently mapped.
  • Properties window for the field listed. These value are configurable:
    • Name: Specify the Column name.
    • Data Type: The data type can be changed according.
    • Length: if the data type specified is a string, the length specified here would be the maximum size. If the data type is not a string, the length will be ignored.
    • Precision: Specify the number of digits in a number.
    • Scale: Specify the number of digits to the right of the decimal point in a number.
    • CodePage: Specify the Code Page of the field.
  • Arrows: Move the fields to a desired location in the file.

Error Handling page

The Error Handling page allows you to specify how errors should be handled when they happen.

SSIS Google BigQuery Destination - error handling

There are three options available.

  1. Fail on error
  2. Redirect rows to error output
  3. Ignore error

When the Redirect rows to error output option is selected, rows that failed to write to the Google BigQuery will be redirected to the ‘Error Output’ output of the Destination Component. As indicated in the screenshot below, the blue output connection represent rows that were successfully written, and the red ‘Error Output’ connection represents rows that were erroneous. The ‘ErrorMessage’ output column found in the ‘Error Output’ may contain the error message that was reported by server or the component itself.

SSIS Google BigQuery Destination - error output