How to read data from Snowflake using REST API and SSIS

REST API Integrations
, ,
1

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:

  1. Register an OAuth application in Snowflake:
    Authenticating to the Server | Snowflake Documentation
  2. 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
  3. Use JSON Source to consume Snowflake data:
    https://zappysys.com/products/ssis-powerpack/ssis-json-file-source
  4. Use Snowflake API documentation for reference:
    Snowflake SQL REST API | Snowflake Documentation
  5. Configure pagination, if needed:
    https://zappysys.com/blog/ssis-rest-api-looping-until-no-more-pages-found/
  6. Refer to these links if you need to deal with multiple or multi-dimensional arrays:
    https://zappysys.com/blog/parse-multi-dimensional-json-array-ssis
    Reading JSON Arrays from file / REST API | ZappySys Blog

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