Not able to write data to Snowflake database using ODBC connector from Azure Data Factory

Question

I am trying to read data from Azure Data Lake Storage and write to Snowflake database using Azure Data Factory pipeline. But I am getting below error. At the same time I am able to read it from Snowflake. I do have write privilege at the database side. I have Integration Runtime installed on the Azure VM, ODBC driver installed on the VM. Able to establish the connection successfully.

Message': 'Failure happened on 'Sink' side. ErrorCode=SystemErrorOdbcWriteError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [07009] [Snowflake][ODBC] (10670) Invalid descriptor index, descriptor record does not exist, or descriptor record was not properly initialized.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=Microsoft.DataTransfer.ClientLibrary.Odbc.Exceptions.OdbcException,Message=ERROR [07009] [Snowflake][ODBC] (10670) Invalid descriptor index, descriptor record does not exist, or descriptor record was not properly initialized.,Source=Snowflake,'',
'EventType': 0,
'Category': 5,
'Data': {
'FailedOdbcDbOperation': 'ERROR [07009] [Snowflake][ODBC] (10670) Invalid descriptor index, descriptor record does not exist, or descriptor record was not properly initialized.',
'FailureInitiator': 'Sink'
},
'MsgId': null,

Answer 1

There is a feature for integrating with this tool being worked on. I do not have an eta.

Answer 2

I just created a temp solution that could trigger a COPY function from snowflake. You would have to export your data as a file in to a Azure Staging area from ADF then use this function to make a call to Snowflake to ingest the new files.

It essentially allows you to pass connection parameters plus a SQL command to snowflake from ADF.

https://github.com/NickAkincilar/Snowflake-Azure-DataFactory-Connector

[snowflake-datawarehouse]