During the last post we discussed how Snowflake ODBC connector use with Azure Data factory to ingest data into Snowflake hosted on AWS cloud platform. To implement the requirement we have installed Snowflake ODBC driver on the machine. Later on we install and configure Azure integration runtime (Self Hosted) on our machine. So now in continuation of the same the next steps requires configuring the ODBC linked service in ADF.
- Create ODBC linked service: When attempting to create an ODBC linked service, I encountered significant difficulties during the configuration process. Configuring the ODBC every time I was getting below error.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.
Finally two things solved the issue:
- DSN=<<odbc name>> should be the same as self hosted IR.
- Create the ODBC connector inside the System Admin as discussed in earlier steps.
- Create the Dataset pointing to the ODBC linked service.
Note: We are able to connect with AWS hosted snowflake account. As part of POC we will be using this dataset as sink and loading the data into CUSTOMER_TABLE_BKP.
- Run the pipeline and observe the behavior.
We know the reason for failure ,so we will focus on ODBC connection status.