‘Failed to execute script. Exception: 'Odbc Operation Failed.’ Error in Script activity in ADF

Harshvardhan Medikonda 5

Hi Team,

We have few pipelines in ADF where we are fetching the Data from multiple API endpoints using Copy Activity. We are connecting to API by leveraging ‘Rest API’ linked service and Integration Runtime(IR) as ‘AutoResolveIntegrationRuntime’. We are able to download the data from API and storing the data in JSON file format in Blob storage. Currently we are facing an error ‘Failed to execute script. Exception: 'Odbc Operation Failed’ while loading the data file from Blob storage to Snowflake Data warehouse using script activity .The pipelines are failing from Monday(05/13) because of this error. It would be helpful if you could help us to resolve this issue.

Error message: ‘Failed to execute script. Exception: 'Odbc Operation Failed.’
Failure type :User configuration issue

phemanth 7,190 Reputation points Microsoft Vendor

2024-05-16T11:08:30.1633333+00:00
@Harshvardhan Medikonda

Thanks for using MS Q&A platform and posting your query.

The error message "Failed to execute script. Exception: 'Odbc Operation Failed'" indicates an issue with the ODBC connection between your Azure Data Factory (ADF) and Snowflake during the script activity. Here are some troubleshooting steps to resolve this user configuration issue:

1. Verify Snowflake Connection:

Check if the Snowflake connection string within the Script activity is valid. Ensure credentials, database name, and schema are correct.

Test the Snowflake connection independently using a tool like Snowflake web UI or Snowflake ODBC driver to confirm connectivity.

2. Script Activity Configuration:

Review the script used in the Script activity. Make sure it's correctly formatted for loading data from JSON to Snowflake.

Double-check if the script uses the correct ODBC connection reference for Snowflake.

3. ODBC Driver Issues:

Verify you're using a compatible ODBC driver for Snowflake. Check for any recent updates or reinstall the driver if necessary.

Ensure the ODBC driver is properly configured on the Integration Runtime used by the ADF pipeline.

4. Firewall Restrictions:

If your environment utilizes a firewall, confirm that communication between ADF and Snowflake isn't blocked. Whitelist necessary URLs or IP addresses for Snowflake access.

5. Temporary Issues:

Occasionally, transient Snowflake issues can cause connection problems. Rerun the pipeline after some time to see if the error persists.

Refer to Microsoft documentation for details on loading data to Snowflake using Script activity in ADF: https://learn.microsoft.com/en-us/azure/data-factory/connector-snowflake

Search online forums for similar issues with "Odbc Operation Failed" in ADF and Snowflake connection: https://stackoverflow.com/questions/69434162/trouble-loading-data-into-snowflake-using-azure-data-factory

Hope this helps. Do let us know if you any further queries.
Somi 0 Reputation points

2024-05-16T13:43:20.28+00:00

Since the 13th of May, I've been encountering a recurring issue where the script activity in the pipeline consistently fails with the same error.
The script when manually run from the Snowflake runs fine but when run through the ADF pipeline, it gets failed.
We are able to get the data into Blob storage from source. To further utilize it in the Script activity, its somehow not working
Somi 0 Reputation points

2024-05-17T05:51:19.77+00:00

Selecting the option from Query to Non Query made it work in the settings of Script Activity
phemanth 7,190 Reputation points Microsoft Vendor

2024-05-17T06:10:40.8133333+00:00
@Harshvardhan Medikonda

Please do the below checks and let us know if issue persists

1. Script Differences:

Since the script runs successfully in Snowflake web UI but fails in the ADF pipeline, there might be subtle differences in how the script is executed in each environment. Double-check the following:

Script Parameters: Ensure any parameters used in the script have the same values when running through ADF compared to the Snowflake UI. Consider using ADF variables to manage parameters and avoid manual intervention.

File Path: Verify that the script in ADF references the correct path to the JSON file in Blob storage. Pay attention to case sensitivity and relative/absolute paths.

Permissions: Confirm that the Integration Runtime user used by ADF has read permissions for the JSON file in Blob storage.

2. ADF Script Activity Configuration:

Resource Limits: Check the resource limitations set for the Script activity in ADF. These limitations can affect execution, especially for complex scripts. Consider adjusting memory or timeout settings if necessary.

Logging: Enable detailed logging for the Script activity in ADF. This can provide more insights into the script execution and potential errors within the ADF environment.

3. Transient Network Issues:

Although less likely given the consistent failures, consider the possibility of intermittent network issues between ADF and Snowflake. Rerunning the pipeline after some time might help if the issue is transient.
phemanth 7,190 Reputation points Microsoft Vendor

2024-05-20T03:33:42.0733333+00:00

@Somi Glad to know your issue has been resolved. thanks for sharing the solution

1 answer

phemanth 7,190 Reputation points Microsoft Vendor

2024-05-20T03:33:15.5366667+00:00

@Harshvardhan Medikonda Welcome to Microsoft Q&A platform and thanks for posting your question.

Can you try with this Solution, Selecting the option from Query to Non-Query made it work in the settings of Script Activity

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.
Please sign in to rate this answer.
Somi 0 Reputation points

2024-05-21T11:10:57.5133333+00:00

@phemanth Thanks, but still would like to know the reason why it started working with the Non-Query option. It was set up with Query option and was running fine since then. Would really appreciate, if I could get an answer to it.

phemanth 7,190 Reputation points Microsoft Vendor

2024-05-22T09:41:19.4666667+00:00

@Somi The Non-Query option, on the other hand, is used when you do not expect a result set from the script execution. This is common for scripts that perform actions such as data manipulation or database administration tasks

If your script started working with the Non-Query option, it could mean that the script does not return a result set, or the result set was not being handled correctly with the Query option

It’s also possible that there were changes in the data, the script, or the database configuration that made the Query option unsuitable.

Implicit DDL Statements: If your script contained any implicit DDL (Data Definition Language) statements like table creation or schema modifications within the "Query" context, it might have faced challenges with the ADF interpretation. "NonQuery" is typically used for DDL statements, so switching to it could have resolved those issues.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

phemanth 7,190 Reputation points Microsoft Vendor

2024-05-23T04:24:02.75+00:00

@Harshvardhan Medikonda Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Sign in to comment

Share via

‘Failed to execute script. Exception: 'Odbc Operation Failed.’ Error in Script activity in ADF

1 answer