Snowflake Connection

Step 1. For this demo's purposes we will be using the SYSADMIN role but the steps work for any user created role.
With ACCOUNTADMIN as your current role, execute GRANT CREATE INTEGRATION ON ACCOUNT TO ROLE SYSADMIN

Step 2. Change your account's role and default role.
To change the current role - USE ROLE SYSADMIN
To change the default role - ALTER USER [Your username] SET DEFAULT_ROLE = 'SYSADMIN'
You can achieve this by using the UI as well.

Step 3. Open a Snowflake worksheet and create an integration, example integration below. Official Snowflake documentation for the integration can be found here.
CREATE SECURITY INTEGRATION [Your integration name]
TYPE = oauth
ENABLED = true
OAUTH_CLIENT = custom
OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
OAUTH_REDIRECT_URI = 'https://www.cozyroc.com/oauth_callback'
OAUTH_ISSUE_REFRESH_TOKENS = TRUE
OAUTH_REFRESH_TOKEN_VALIDITY = 86400

Step 4. You're almost ready, now to check the Id and Secret of your Integration, just execute SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS( [Your integration name] ) and you can input them in the connection manager.

Step 1. Run "Visual Studio" under "Administrator account". Right click on Connection managers tab and select New Connection. In Add SSIS Connection Manager dialog scroll down, select REST connection manager from the list and then click Add.

Step 2. From the drop-down menu Configuration select Snowflake. Enter a valid Account URL for the Server parameter, you can copy it from the Snowflake UI. The Account URL should look something like this "https://fo32815.switzerland-north.azure.snowflakecomputing.com". Add "/api/v2" at the back of your server URL. Alternatively you can replace the [account identifier] placeholder with your account id separated with a dash e.g. ORGID1-ACCID1.

Step 3. In the Token file row click "New" and input the integration Secret and Id, if you do not know what that is refer to the Snowflake documentation or the part about Snowflake in this documentation. Next copy and paste the integration Redirect_uri and if you have a Schema in which you will be working, specify it and the database it's located in.

Step 4. Press the Test Connection button. When you see Test Connection succeeded, click OK.

Congratulations! You have successfully created a connection to the Snowflake API using COZYROC's REST Connection Manager.

Step 1. Generate as a minimum, a 2048-bit RSA key pair. After navigating to a proper folder, open a terminal or command prompt and execute:

openssl genpkey -algorithm RSA -out private.key -aes256

Note: The generated private.key file contains the encrypted RSA private key. Keep this file secure and never share it with unauthorized individuals.

Step 2. Generate a certificate signing request (CSR) with the command:

openssl req -new -key private.key -out certificate.csr

Step 3. Generate a self-signed certificate:

openssl x509 -req -days 365 -in certificate.csr -signkey private.key -out certificate.crt

Step 4. Combine the private key and the certificate into a PFX/P12 file:

openssl pkcs12 -export -out certificate.pfx -inkey private.key -in certificate.crt

Step 5. Extract the public key from the private key since we need the public key for Snowflake:

openssl rsa -in private.key -outform PEM -pubout -out public_key.pem

Step 6. Assign the public key to a Snowflake user:

ALTER USER jsmith SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';

Note: Only users with the SECURITYADMIN role or higher can alter a user.

Step 1. Run "Visual Studio" under "Administrator account". Right click on Connection managers tab and select New Connection. In Add SSIS Connection Manager dialog scroll down, select REST connection manager from the list and then click Add.

Step 2. From the drop-down menu Configuration select Snowflake. Enter a valid Account URL for the Server parameter, you can copy it from the Snowflake UI. The Account URL should look something like this "https://fo32815.switzerland-north.azure.snowflakecomputing.com" replace the default value and add "/api/v2". Your server field should look like the one in the image. Alternatively you can replace the [account identifier] placeholder with your account id separated with a dash e.g. ORGID1-ACCID1.

Step 3. Input the proper values in the connection manager.
For the PrivateKey field input the path to your .pfx file and for the private key password - provide the value you used upon generation.

Step 4. The account identifier and username
For the account identifier, follow the image and copy it from the website.
NOTE You have to delete the "." symbol separating the two values and replace it with a "-" symbol, so it would look like this - NVVA4TE-UD16253
For the username, just copy the username you login into your account.

Step 5. The fingerprint
You can get the fingerprint by executing DESC USER [Your Username]; and copying the RSA_PUBLIC_KEY_FP property value.

Step 6. Press the Test Connection button. When you see Test Connection succeeded, click OK.

Congratulations! You have successfully created a connection to the Snowflake API using COZYROC's REST Connection Manager.

Step 1. Select an existing or create a new "Data Flow Task".

Step 2. Open "SSIS Toolbox" and drag and drop COZYROC "REST Source" component.

Step 3. Double click on the "REST Source" component and select the Snowflake connection from the list. Select from the available resources in the second list. Enter the name of the warehouse you created earlier and if you want to have a statement that is different from the default SELECT * FROM [Your table name], specify it in the Statement field.

Step 4. Click on the "Columns" tab and select the Snowflake columns you want to read.

Step 5. Click "Preview" to make sure all necessary resource columns are included and you are getting data.

Bravo! You have now learned how to read data from the Snowflake API service.

For the purpose of this demo, we will be using a query which utilizes the database SNOWFLAKE_SAMPLE_DATA, allowing anyone to execute it.

Step 1. Using the Custom Query resource, paste your statement. An example statement (you may need to remove the line breaks, a tool such as this can be very helpful):

SELECT 
    O.O_ORDERKEY, 
    array_agg(
        OBJECT_CONSTRUCT(
            'O_COMMENT', O_COMMENT, 
            'O_ORDERDATE', O_ORDERDATE, 
            'O_TOTALPRICE', O.O_TOTALPRICE
        )
    ) AS order_details, 
    array_agg(
        OBJECT_CONSTRUCT(
            'L_SUPPKEY', L.L_SUPPKEY, 
            'L_COMMITDATE', L.L_COMMITDATE, 
            'L_COMMENT', L.L_COMMENT, 
            'L_PARTKEY', L.L_PARTKEY, 
            'L_ORDERKEY', L.L_ORDERKEY, 
            'SOMEOBJECT', OBJECT_CONSTRUCT(
                'COMMENT', L.L_COMMENT, 
                'COMMITDATE', L.L_COMMITDATE, 
                'SOMEARR', ARRAY_CONSTRUCT(
                    OBJECT_CONSTRUCT('L_COMMENT', L_COMMENT), 
                    OBJECT_CONSTRUCT('L_DISCOUNT', L_DISCOUNT)
                )
            )
        )
    ) AS lineitem_details
FROM 
    SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS O 
JOIN 
    SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.LINEITEM L 
ON 
    O.O_ORDERKEY = L.L_ORDERKEY 
WHERE 
    O.O_ORDERKEY < 50 
GROUP BY 
    O.O_ORDERKEY

Step 2. Make sure the expected outputs and their columns are created. Then check which columns you wish to show up in each output.

Step 3. Check if you have the correct output preview and don't worry if some columns are showing up as JSON text.

Step 4. Finally, execute the package and check the results.

Step 1. Select an existing or create a new "Data Flow Task".

Step 2. Open the "SSIS Toolbox" and drag and drop COZYROC "REST Destination" component.

Step 3. Double click on the "REST Destination" component and select the Snowflake connection from the list. After that select the action you will be performing with your data and select the target Resource. Don't forget to set your warehouse for the Warehouse parameter.

Step 4. Click on the "Mapping" tab and map the input columns for the process you are configuring.
If you want to provide input for other resource composite objects, change the input above and map the columns.

Done. You have now learned how to write data into the Snowflake API resource.