CODEWITHSUNDEEP
snowflake-cloud-data-platformazure-data-factory
Nipun Dixit
Nipun Dixit

Reputation: 13

Snowflake ODBC connector in ADF error - ODBC query not valid

I am trying to create an ADF activity that can truncate the target snowflake table before a for each loop copies the blob files to it. I can't use the pre copy because it will clean up the table in each iteration. Interestingly when i use a lookup to truncate the table it throws an error hat the ODBC query is not valid however it does the job at database level and truncates the table. Has anyone encountered a similar error.

Upvotes: 1

Views: 1372

Answers (1)

David Garrison
David Garrison

Reputation: 2880

Yes we had this same issue with ADF using the ODBC connector. It seems to be a known bug. The same thing happens for any UPDATE/INSERT/DELETE statement.

Solutions we used:

  1. Use the newer Native Linked Service Connector. Recommended.
  2. Do your truncate in a procedure and use a Lookup activity to just call the procedure
  3. Prior to the native connector, in some places we would follow up the Truncate activity with a "On Completion" dependency, and have that do a lookup to validate that the table is actually empty so that it could throw an error and stop the pipeline if it didn't work. This was not ideal because it requires an extra ping to the database, and some wacky ADF flow management.

Upvotes: 0

Related Questions