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

Specifies the 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

Specifies the 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

Specifies the maximum lifetime (in seconds) of a connection. 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 exceeds the value specified by Connection Lifetime. The default value is 0.

Connection Timeout

Specifies 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 60.

Database

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

Default Command Timeout

Specifies 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

Specifies the Snowflake account domain.

Failover Retries

Specifies 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.

Local SQL Engine

Is not used for Snowflake.

Max Pool Size

Specifies 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

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

Passphrase

Specifies the passphrase used to decrypt the private key file specified by the Private Key parameter.

Password

Specifies the password to log in 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

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

Private Key

Specifies the path to the private key file used for key pair authentication with Snowflake.

Proxy Host

Specifies the host name or IP address of the proxy server.

Proxy Password

Specifies the password for the proxy server account.

Proxy Port

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

Proxy User

Specifies the proxy server account name.

Readonly

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

Refresh Token

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

Role

Specifies the role name used to connect.

Schema

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

Text36AsGuid

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

Text38AsGuid

Specifies whether to interpret values in the columns of the STRING (38) type as Guid values.

Token Server

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

User

Specifies the user name to log in with. Used only for Basic authentication.

UTC Dates

Converts dates to UTC format during import if set to True, and treats the date values entered by the user (in SELECT statements, etc.) as UTC dates when sending them to the data source.

Warehouse

Specifies the name of the warehouse used for a database.