Using the Snowflake Source Component

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

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

  • General
  • Columns

General Page

The General Page of the Snowflake Source Component allows you to specify the general settings of the component.

Snowflake Source - General Page

Connection Manager

The Snowflake Source Component requires a Snowflake connection manager.

Database

The Database drop-down menu displays a list of available databases in the Snowflake instance defined in the Connection Manager. Selecting a database here will automatically populate the Schema drop-down list.

Schema

The Schema drop-down menu displays a list of available Schemas in the previously selected Snowflake database. Selecting a schema here will automatically populate the Table drop-down list.

Table

The Table drop-down menu displays a list of available tables from the schema specified in the previously selected database schema. Selecting a table here will automatically populate the Command property. This is a quick and easy way to generate a basic select statement for reading from the database.

Command Timeout

The Command Timeout option allows you to specify the number of seconds for the command timeout values. The default value is 120 seconds.

Command

The Command textbox is the command text that will be executed over the Connection Manager to read data from the database. A basic select statement can easily be generated by selecting a table from the Data Source property. You can then further customize the command to your liking to perform powerful queries.

Command textbox supports the use of User and System Variables. Simple select a variable under the Insert Variables drop down menu, and a placeholder value will be inserted into the filter text.

Import

Loads SQL from a file into the Command property.

Export

Save the SQL 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

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 Snowflake Source Component shows you the available columns based on the settings in the General page.

Snowflake Source - Columns Page

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

Note: As a general best practice, you should only select the fields that are needed for the downstream pipeline components. Do this in the columns page using the checkboxes or in the General page by removing the column from the command entirely.