Snowflake

ExpandedToggleIcon        Connection Dialog

The following connection options are required for connecting to Snowflake:

1.Domain - a Snowflake account domain.
2.User Id -  a user name to log in with.
3.Password - a password to log in with.
4.Database - a database name.

 

Entering these options is enough for reading data from Snowflake and loading data to Snowflake using normal Insert, Update, and Delete operations. You may also specify the following additional pareameters:

1.Warehouse - a name of the warehouse used for a database.
2.Schema - a name of the schema to be used once a connection is opened.
3.Role - a role name used to connect.

Note that if you want to use a high-performance data loading for Snowflake, you will also need to specify the storage service to use - Amazon S3 or Azure Blob Storage and the parameters to connect to the corresponding services in the Devart Snowflake Destination component editor.

In addition to the required options, Advanced connection options may be set for more fine connection tuning.

 

ExpandedToggleIcon        Advanced Connection Options

The following table describes the options that you can include in the Connection String using the Advanced tab of Connection Editor dialog box:

 

Option

Description

AuthenticationType

Determines the authentication type to use - Basic or OAuth2.

Client ID

OAuth client ID. Used for OAuth 2.0 authentication only. A Snowflake administrator can obtain the client ID by executing the DESC SECURITY INTEGRATION command for a security integration. A new security integration can be created using the CREATE SECURITY INTEGRATION command. You can find more information about OAuth authentication in the Snowflake blog.

Client Secret

OAuth client secret. Used for OAuth 2.0 authentication only. A Snowflake administrator can obtain the client secret by executing the SHOW_OAUTH_CLIENT_SECRETS function for a security integration.

Connection Lifetime

When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. The default value is 0.

Connection Timeout

The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. The default value is 15.

Database

The name of the Snowflake database you want to connect to.

Default Command Timeout

The default time in seconds to wait while trying to execute a command before terminating the attempt and generating an error. 0 indicates no limit.

Domain

The Snowflake account domain

Failover Retries

The number of retry attempts to re-run a command in case when the command failed because of an exception, that indicates that the reason of the failure may be temporary.

Host

The name or IP address of host of the Snowflake database to which to connect.

Ignore Unnamed Parameters

Determines whether to treat the '?' character in the command text as an unnamed parameter.

Local SQL Engine

Not used for Snowflake.

Max Pool Size

The maximum number of connections allowed in the pool. Setting the Max Pool Size value in the connection string can affect performance. The default value is 100.

Min Pool Size

The minimum number of connections allowed in the pool. The default value is 0.

Password

The password to login with. Used only for Basic authentication.

Persist Security Info

Indicates if security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state.

Pooling

If true, by default, the connection is drawn from the appropriate pool or is created and added to the appropriate pool.

Port

Snowflake connection port; default value is 5432.

Protocol

The Frontend/Backend Protocol version. Available values are Ver20 and Ver30. Set the parameter to Ver20 for the protocol version 2.0 or to Ver30 for protocol version 3.0.

Proxy Host

The host name or IP address of proxy server.

Proxy Password

The password for the proxy server account.

Proxy Port

The port number of the proxy server. The default value is 3128.

Proxy User

The proxy server account name.

Readonly

Indicates whether the connection is read-only (allows only SELECT statements).

Refresh Token

OAuth 2.0 refresh token for Snowflake. Used only for OAuth2 authentication.

Role

The role name used to connect

Schema

The name of the schema to be used once a connection is opened.

Text36AsGuid

This parameter specifies whether to interpret values in the columns of the STRING (36) type as Guid values.

Token Server

OAuth Refresh Token may expire after some time. SSIS Data Flow Components for Snowflake can query new tokens automatically and store them between sessions if you set this parameter. This parameter specifies the location to store new tokens between sessions. You can specify a file or a registry key to store tokens. For example, "file://D:\temp\oauth_token_1234.txt" or "reg://HKEY_CURRENT_USER\Software\Devart\dotConnect\OAuth_12345"

User

The user name to login with. Used only for Basic authentication.

UTC Dates

If set to True, dates will be converted to UTC format during import, and the date values entered by the user (in SELECT statements, etc.) are considered UTC dates when sending them to the data source.

Warehouse

The name of the warehouse used for a database.