Load the data from SQL server to SNOWFLAKE table using SSIS

We can load the SQL server table data into the SNOWFLAKE using SSIS. To load the data we are using the ODBC destination to connect the SNOWFLAKE and loading it.

Before creating the package we need to create the ODBC DSN.

Read here: Creating ODBC Connection for the SNOWFLAKE

Taking Data flow task in SSIS.  

Now taking ODEDB Source to get the data from the SQL server table. 

Now taking ODBC Destination.

Before that we are creating the ODBC connection.

Right click on the connection manager pan  

Click on the new connection and select the ODBC Connection and click on the add button.

Read here: Creating ODBC Connection for the SNOWFLAKE

https://bageshkumarbagi-msbi.blogspot.com/2022/09/creating-odbc-connection-for-snowflake.html

Select the DSN name and provide the SNOWFLAKE User name and password and click on the Test Connection button to test the connection. 

Click Ok. ODBC Connection is created successfully.

Now taking Destination as ODBC destination and selecting connect as ODBC.  

Providing the table name  

Doing the column mapping.  

Click ok.

Our package is ready to run.

See the source data. 

 See the destination table 

                         

Now running the package.

Package executed successfully.    

Now see the records into the SNOWFLAKE table.  

1k rows loaded successfully.

Creating ODBC Connection for the SNOWFLAKE

 When we are creating the DSN for ODBC connection to connect the Snowflake, First time we need to check that snowflake odbc driver install in our system or not.  Without this driver we can’t create the DSN.

First we need to install the Driver. Below is the official website to download the driver.

https://sfc-repo.snowflakecomputing.com/odbc/win64/latest/index.html    

After downloading the driver we need to install this.

Let’s create the DSN

Open ODBC Data Source Administrator and select User DSN and click on the Add button.    

Select SnowflakeDSIIDriver  

Click finish button                

Here we need to provide all information. After filling all details click on the Test button.  

Now click ok.

DSN Created successfully.