Help Manual - Google Sheets Source

Documentation on SSIS Google Sheets Source component

Google Sheets Source Component

The Google Sheets Source Component is an SSIS data flow pipeline component that can be used to read/retrieve data from a Google Sheets spreadsheet.

The component includes the following two pages to configure how you want to read data.

  • General
  • Columns

General Page

The General page of the Google Sheets Source component allows you to specify the general settings of the component.

SSIS Google Sheets Source Component

Connection Manager

The Google Sheets Source component requires a connection in order to connect to the Google Sheets. The Connection Manager drop-down will show a list of all connection managers that are available to your current SSIS packages.

Configure Source
Sheets

Specify the Google Sheets you want to work with.

Named Ranges

Specify the Named Ranges defined in the Google Sheets.

Start Row

Use the Start Row to indicate which row to start from.

Number of Rows

Specify how many rows you wish to retrieve.

Read to End

When this option is checked, the component will read all rows from the Start Row you have specified to the last row of the sheet. Once this option is checked, the Number of Rows will be greyed out.

Column Names

There are four options that you can use to indicate Column Names:

  • Not specified: Choose this option if the sheet does not contain column names
  • First row of sheet: Choose this option if the first row contains column names
  • Start row: Choose this option to specify the column name based on the Start Row
  • First row of named range: This option is only available when Named Ranges source is selected, the first row in the named range will be used as column names.
Value Render Option

The Value Render Option allows you to determine how values should be rendered in the output. There are three options available:

  • FORMATTED_VALUE: values will be calculated & formatted in the output according to the cell's formatting.
  • UNFORMATTED_VALUE: value will be calculated, but not formatted in the output.
  • FORMULA: value will not be calculated and it will return the actual formulas defined in the cell.
Date Time Render Option

The Date Time Render Option allows you to determine how date, time, datetime, and duration fields should be rendered in the output. There are two options available:

  • SERIAL_NUMBER: date, time, datetime, and duration fields will be returned as doubles in "serial number" format. The whole number portion of the value (left of the decimal) counts the days since December 30th, 1899. The fractional portion (right of the decimal) counts the time as a fraction of the day.
  • FORMATTED_STRING: date, time, datetime, and duration fields will be returned as strings in their given number format.
Refresh Component

By clicking the Refresh Component button, the component will retrieve the latest metadata from the Excel File you have specified in the Excel Connection Manager. After clicking this button, you will receive a status message indicating how many fields have been updated, added, or deleted.

Expression fx Button

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

Generate Documentation Button

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

Column Page

The Columns page of the Google Sheets Source Component shows you all available attributes from the source that you specified on the General page.

SSIS Google Sheets Source Component - Columns Page

On the top left of the grid, the checkbox can be used to toggle the selection of all available fields. This is a productive way to check or uncheck all available fields.

The Columns page grid consists of:

  • Google Sheets Field: Column that will be retrieved from Google Sheets.
  • Data Type: The data type of this field.
  • Properties window for the field listed:
    • Name: specify the column name.
    • Data type: the data type can be changed accord.
    • Length: Specify the Length of the fields. 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.
  • + sign: add field to Excel File.
  • - sign: Remove field from Excel File.
  • Arrows: move the fields to a desired location in the file.