Azure Data Factory Copy Activity stopped working between 2am - 3am Sep 7th.

Question

I have had scheduled copy jobs going every 45 minutes for the past month.

On Saturday September 7th, between 2:00am and 3am, the copy activity just stopped working.

I have a copy activity in a switch case. The error message on the switch case says

Error codeActionFailed

Failure typeUser configuration issue

DetailsActivity failed because an inner activity failed

The copy activity uses a sql query. The "preview data" button in the "Source" section of the copy activity fails with:

Expected number for array index, found HardCodedText

The same sql query works as normal in a Lookup block. And there is no problem previewing data when "Use Query" is set to "Table"

The query, which works in a Lookup, and worked up until Saturday between 2am and 3am:

What changed, and why does this query no longer work in a copy block?

Answer 1

Possible Causes

1. Internal Configuration or Update in ADF: Sometimes, service updates can roll out that may unintentionally introduce breaking changes to existing pipelines. The change in behavior of the "Preview Data" feature and error involving "HardCodedText" suggests that something may have changed in how ADF handles SQL queries within Copy Activities.
2. Query Parsing Issue: The error "Expected number for array index, found HardCodedText" implies that ADF is having trouble interpreting the query syntax in the Copy Activity. This could be related to how parameters or dynamic content are processed in that specific block. The same query working in a Lookup activity but failing in a Copy activity suggests a possible difference in how these activities parse or execute SQL queries.
3. Source Schema Changes: Ensure that no changes were made to the schema of the source database between 2:00 AM and 3:00 AM. Sometimes, even minor changes in the source table structure (column types, constraints) can cause unexpected failures in ADF pipelines.
4. Temporary Connectivity Issue: While less likely, if the ADF instance had any connectivity issues or downtime during that period, it could have impacted the scheduled run.

Suggested Troubleshooting Steps

1. Check the SQL Query and Parameters:
   • Ensure that there are no dynamic parameters in your query that are causing the parsing error in the Copy activity.
   • If you're using any expressions or variables within the query, check their syntax and value.
2. Compare Configuration Between Lookup and Copy Activity:
   • Review the configurations of both activities (Lookup and Copy) and ensure that they are identical where they need to be (e.g., the way the query is set up, the connection to the source).
   • If the Copy activity uses any advanced settings or features (like mapping or transformations), temporarily disable them to see if the basic query works.
3. Test the SQL Query in a New Copy Activity:
   • Try creating a fresh Copy activity with the same SQL query to see if the issue persists. If it does, this can help rule out potential issues related to other activities in the switch case or pipeline.
4. Monitor Logs and ADF Alerts:
   • Check the logs in ADF for more detailed error messages around the time of failure.
   • You can also enable diagnostic logging in Azure Monitor to capture more granular details about the failure.
5. Try Using "Table" Instead of "SQL Query" Temporarily:
   • Since using "Table" in the source section works fine, you can temporarily switch to that if it's feasible, while investigating why the query fails specifically in the Copy activity.