Requirement
Snowflake as source or destination when using in SSIS as ETL tool.
Tools needed.
- Snowflake ODBC 32-bit driver - download link - ODBC - Snowflake Developers
- Snowflake account.
- SSDT (SQL Server Data Tools) with Visual Studio.
Install & Configure Snowflake ODBC Driver
Install only 32 bit ODBC driver if SSIS is 32 bit installed. In my case I have installed 64 bit ODBC driver first and find issues in SSIS as it was not showing in the ADO.NET task after selecting ODBC as connection.
After I installed 32-bit ODBC driver, I am able to see the DSN in my ODBC source.
Create DSN (data source name)
After installed Snowflake DSN, open the ODBC Data Sources (32-bit) by typing in the start in your PC.
Below screen will come, here we need to add new DSN using Snowflake driver.
In User DSN tab, click Add and look for Snowflake driver and select it and click finish.
Provide the connection details of your Snowflake account and click Test.
DSN is created successfully.
Configure ODBC Source In SSIS
Create new SSIS package and select ODBC Source as shown below:-
Create new ODBC connection and configure to select the DSN created before as shown below:-
After connection setup properly, when we use to look for the tables in the source it is throwing the error.
This is kind of known issue, and some databases ODBC connection does not work.
Let's switch to ADO NET source to connect the DSN.
Configure ADO NET Source In SSIS
Select the ADO NET source and configure as shown below: -
After selecting ODBC driver in above screen below screen will appear and where after selecting the DSN created by us in above step can be selected and tested the connection.
Now able to preview the list of tables available in the database.
Pipeline/Package Validation
After enabling the data viewer in the SSIS, we can see the data when pipeline/package is executed.
Another way by checking the executed query on the Snowflake Activity under Query History
This is how we can connect Snowflake using SSIS.
No comments:
Post a Comment