How to Insert Bulk Data Using Snowflake Integration Service

Help Integration Service
, ,
1

I’m trying to insert bulk data using the new Snowflake Integration Service Connector. Am I just missing something or is that not an option with the current package? (v4.0.0-preview)…

If it is not an option, what is a feasible workaround?

image

2

Hi,

Chat GPT output(you can probably ask different questions):

The Snowflake Scope activity in UiPath is used to define a set of activities that will execute within a specific Snowflake connection. This allows you to perform multiple actions, such as executing queries and loading data, within a single transaction. It also allows you to manage the Snowflake connection more efficiently by automatically opening and closing the connection as needed.

Here is an example of how you can use the Snowflake Scope activity in UiPath:

  1. Drag the Snowflake Scope activity from the activities panel to the design area.
  2. Inside the Snowflake Scope activity, drag the activities that you want to execute within the Snowflake connection. For example, you can add the “Snowflake Execute Query” or “Snowflake Bulk Insert” activity.
  3. In the properties panel, configure the Snowflake connection by providing the required information such as the account, username, password, and warehouse.
  4. Run the workflow. The Snowflake Scope activity will automatically open the connection, execute the activities within the scope, and then close the connection.
  5. If you have multiple Snowflake Scopes in your workflow, you can set a transaction isolation level for each scope. The isolation level determines how the data is locked during the transaction.

You can also use the “Snowflake Transaction” activity, which allows you to start, commit or rollback a transaction.

It’s worth noting that the Snowflake Scope should be used when the actions that you want to perform requires to be in the same transaction, if not, it’s better to open and close the connection for each action

There are a few ways to insert bulk data into a Snowflake table using the Snowflake Integration Service in UiPath:

  1. Using the “Snowflake Bulk Insert” activity: This activity allows you to insert data into a Snowflake table from a CSV or Excel file. It supports loading data in bulk and can handle large data sets efficiently.
  2. Using the “Snowflake Execute Query” activity: You can use this activity to execute a SQL query that performs a bulk insert. You can use the “COPY INTO” statement to insert data into a Snowflake table from a CSV or JSON file.
  3. Using the “Snowflake Load Data” activity: This activity is designed to load data into a Snowflake table from a CSV or JSON file. It uses the “COPY INTO” statement to insert data in bulk and automatically handles large data sets.

Here is an example of how you can use the “Snowflake Execute Query” activity to insert bulk data from a CSV file into a Snowflake table:

sqlCopy code

COPY INTO my_table
FROM 's3://my-bucket/data.csv'
CREDENTIALS = (AWS_KEY_ID='access_key_id' AWS_SECRET_KEY='secret_access_key')
CSV 
HEADER 
QUOTE = '"'
ESCAPE = '\'

Note that above is an example, you should replace the table name, file path, and credentials with your own.

You should also make sure that the file format, column names, and data types match the table’s schema. Additionally, make sure that the Snowflake user has the correct permissions to access the table and the file location.

It’s important to note that before inserting the data, you should make sure that your data is well formatted and clean, this will help you to avoid errors and issues during the insertion process

3

What Snowflake Package version are you using? I don’t have those activities (Snowflake Execute Query, Snowflake Bulk Insert)

4

Looks like this is not possible with the current package. Also found other bugs in even the Insert Record activity.

We decided against utilizing the Snowflake Integration Service connector until it is improved. Instead, we used ODBC and the regular Database connections (see link below)

5

@Caleb_D How to get this snowflake activity ? and on which version of the UiPath studio will we get the snowflake activities to install. I am using 2021 version and its not giving any option for snowflake activity