Snowflake allows to read data from Snowflake using their Snowflake SQL REST API. However, currently, we don’t have a standalone Snowflake API Connector (check our other API Connectors), so you will need to use JSON Source and OAuth Connection Manager and configure them manually to get data from Snowflake tables. Here are the guidelines on how you could proceed:
- Register an OAuth application in Snowflake:
Authenticating to the server | Snowflake Documentation - Use the OAuth application information to configure OAuth Connection Manager:
How to authenticate to an API with OAuth 2.0 using SSIS / ODBC | ZappySys Blog
SSIS OAuth Connection Archives | ZappySys Blog - Use JSON Source to consume Snowflake data:
https://zappysys.com/products/ssis-powerpack/ssis-json-file-source - Use Snowflake API documentation for reference:
Snowflake SQL API | Snowflake Documentation - Configure pagination, if needed:
How to do REST API Pagination in SSIS / ODBC Drivers | ZappySys Blog - Refer to these links if you need to deal with multiple or multi-dimensional arrays:
Parse JSON array in SSIS or ODBC Drivers | ZappySys Blog
https://zappysys.com/blog/extract-read-multiple-arrays-from-json-data-file-rest-api-response
If you get stuck and need help in configuring, just contact us on the Support page and let us know if you need further help.
OTHER USEFUL LINKS:
Parameterization
REST API Task (for making a single REST API request)
WEB API Destination (for making multiple requests to a REST API)
Deploying package to a server
Placeholder functions
SSIS PowerPack Documentation
ZappySys Blog