Execute Update command in ADF to Snowflake

Question

Hello folks,

I was executing update command in ADF to change a snowflake object.
Options I tried:

1. Firing an update command in the pre-Script of copy activity but faced with syntax error, while the same query is executing in Snowflake successfully.
2. Firing an update command in the Lookup, but was welcomed with the following error:

Failure happened on 'Source' side. ErrorCode=UserErrorOdbcInvalidQueryString,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The following ODBC Query is not valid: 'UPDATE xxxxxx' ,Source=Microsoft.DataTransfer.Runtime.GenericOdbcConnectors,'

Also, I am forbidden to use stored procedure or batch service.

Would really appreciate the help.

Answer 1

Hi @Mahima Lalwani ,

Thanks for your query and using Microsoft Q&A forum.

The error message you are receiving is because you are executing an update query in Lookup activity.

It is by design that lookup activity is used to look up data, which means the expectation is that we will execute a query which would returned data (like SELECT query or Stored procedure that returns data), but not a query or SP that doesn't return data.

I was able to reproduce the same behavior with SQL Update query using lookup activity as I don't have a Snowflake instance and below error is thrown when I ran lookup activity with update query

Error : *Failure happened on 'Source' side. ErrorCode=SqlInvalidDbQueryString,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The specified SQL Query is not valid. It could be caused by that the query doesn't return any data. Invalid query: 'UPDATE [dbo].[emp] SET [FirstName] = 'Kumar' ,[LastName] = 'Kumar' WHERE ID = 16',Source=Microsoft.DataTransfer.ClientLibrary,'*

But what I have noticed is that the table was updated even though lookup activity failed as there is no query output returned. Could you please check if your Snowflake table was updated or not?

AFAIK the pre-Script of copy activity should help to run the update query. Please do share the error message which you received when running pre-script in copy activity. So that we can better assist you on this requirement.

In case if you would like to go with other options, you can also use generic ODBC connector to update/insert Snowflake table.

Additional info: Prerequisites for Snowflake ODBC driver

Hope this info helps.

----------

Thank you
Please do consider to click on "Accept Answer" and "Upvote" on the post that helps you, as it can be beneficial to other community members.

Answer 2

Hi, I'm facing this same issue.
I'm trying to execute an update on the just inserted rows, so I can't do it on the Pre-script.
An error is raising but data is being update though.
I guess that this is happening because nothing is being returned after the update and the Lookup expects something to return.
Is there any some way to execute a simple update on existing data without reloading it in 2 steps?

Thanks