Today, all our pipelines that use an external table (dedicated sql pool) failed due to an strange error message.

Question

Today, all our pipelines that use an external table (dedicated sql pool) failed due to an strange error message.

RickMsBi 86

The error message reads:

Operation on target Import data failed: Activity failed because an inner activity failed; Inner activity name: Transfer xxxxxxx, Error: Execution fail against sql server. Please contact SQL Server team if you need further support. Sql error number: 105221. Error Message: COPY statement input file schema discovery failed: Cannot bulk load file split because either secret or file_properties option is missing.

We also get this error is we simply do a "select" from said tables. To be clear, we're not using a copy into statement, just a select from the external table.

It seems like doing a select from an external table that was created in the dedicated sql pool triggers an internal copy into statement that in return results in an error.

It's for all our pipelines, even dropping and recreating the external tables results in the same error. We didn't do any releases to production. The fact that alle the pipelines suddenly have exactly the same error makes us think this must be a back-end issue.

Amira Bedhiafi 31,391 Reputation points

2023-08-22T11:19:54.16+00:00

The error message seems to hint at a problem in the configuration or underlying infrastructure, possibly related to Azure Synapse Analytics.

Firstly, check the Azure status page for any ongoing incidents or maintenance that might affect your region's Azure Synapse Analytics or related services.

Since the error message explicitly asks to contact the SQL Server team, it would be advisable to reach out to Azure Support directly. They will have the tools and information needed to diagnose and resolve this issue.

Reviewing the external data source configuration might be worthwhile, especially the credentials or secrets required to access the external data. The error mentioning "either secret or file_properties option is missing" could imply some misconfiguration or expiration of secrets.

Try to dig deeper into logs through Azure Monitor or other logging mechanisms you may have in place. Detailed logs may provide additional clues as to what might be going wrong.

Otherwise, try to replicate the issue in a different environment, like a staging or test environment. This might help you identify whether the issue is specific to the production environment or a more widespread problem.
RickMsBi 86 Reputation points

2023-08-22T11:36:17.08+00:00

Thing is, the same issue is apparant on all environments (dev, acc, and prd). All the issues are with external tables in the dedicated SQL pools. We're are using managed identities which shouldn't give any issues, neither are we seeing any issues in the logs. Feels like's there's been an update to the functionality of some sort. Querying the same files using openrowset from serverless doesn't give any issues. Even using the copy-into statement ourselves as a test, and copy the data into a dedicated sql table works without issues. So, that's what we resorted to, we're now directly copying the data into dedicated tables instead and have our reporting team use those. This was on our list anyway. But still, though we have found a workaround, the issue is still there and might need some investigation.

Unfortunately we don't have a support contract and I'm not in the position to change that.
Bhargava-MSFT 31,241 Reputation points Microsoft Employee

2023-08-22T21:49:00.4666667+00:00

Hello RickMsBi,

Welcome to the Microsoft Q&A forum.

Sorry for the inconvenience.

Multiple customers are facing this issue. Could you please let us know your synapse region?

Meanwhile, I am checking with the Product team on this issue and will get back to you as soon as I hear from them.

I appreciate your patience.
Bhargava-MSFT 31,241 Reputation points Microsoft Employee

2023-08-22T23:31:03.5366667+00:00

Hello RickMsBi,

The issue is mitigated. Can you please try to pause/resume and then retry again?

Please let me know if you still see the error.
RickMsBi 86 Reputation points

2023-08-23T07:28:24.8033333+00:00

Hi , the issue is indeed mitigated and our pipelines ran normally this morning!

The issue was in Western Europe.

Thanks!

Regards,

Rick

Accepted answer

0 additional answers

Your answer

Amira Bedhiafi 31,391 Reputation points

2023-08-22T11:19:54.16+00:00

The error message seems to hint at a problem in the configuration or underlying infrastructure, possibly related to Azure Synapse Analytics.

Firstly, check the Azure status page for any ongoing incidents or maintenance that might affect your region's Azure Synapse Analytics or related services.

Since the error message explicitly asks to contact the SQL Server team, it would be advisable to reach out to Azure Support directly. They will have the tools and information needed to diagnose and resolve this issue.

Reviewing the external data source configuration might be worthwhile, especially the credentials or secrets required to access the external data. The error mentioning "either secret or file_properties option is missing" could imply some misconfiguration or expiration of secrets.

Try to dig deeper into logs through Azure Monitor or other logging mechanisms you may have in place. Detailed logs may provide additional clues as to what might be going wrong.

Otherwise, try to replicate the issue in a different environment, like a staging or test environment. This might help you identify whether the issue is specific to the production environment or a more widespread problem.
RickMsBi 86 Reputation points

2023-08-22T11:36:17.08+00:00

Thing is, the same issue is apparant on all environments (dev, acc, and prd). All the issues are with external tables in the dedicated SQL pools. We're are using managed identities which shouldn't give any issues, neither are we seeing any issues in the logs. Feels like's there's been an update to the functionality of some sort. Querying the same files using openrowset from serverless doesn't give any issues. Even using the copy-into statement ourselves as a test, and copy the data into a dedicated sql table works without issues. So, that's what we resorted to, we're now directly copying the data into dedicated tables instead and have our reporting team use those. This was on our list anyway. But still, though we have found a workaround, the issue is still there and might need some investigation.

Unfortunately we don't have a support contract and I'm not in the position to change that.
Bhargava-MSFT 31,241 Reputation points Microsoft Employee

2023-08-22T21:49:00.4666667+00:00

Hello RickMsBi,

Welcome to the Microsoft Q&A forum.

Sorry for the inconvenience.

Multiple customers are facing this issue. Could you please let us know your synapse region?

Meanwhile, I am checking with the Product team on this issue and will get back to you as soon as I hear from them.

I appreciate your patience.
Bhargava-MSFT 31,241 Reputation points Microsoft Employee

2023-08-22T23:31:03.5366667+00:00

Hello RickMsBi,

The issue is mitigated. Can you please try to pause/resume and then retry again?

Please let me know if you still see the error.
RickMsBi 86 Reputation points

2023-08-23T07:28:24.8033333+00:00

Hi , the issue is indeed mitigated and our pipelines ran normally this morning!

The issue was in Western Europe.

Thanks!

Regards,

Rick

Answer 1

Bhargava-MSFT 31,241 Microsoft Employee

Hello RickMsBi,

Root cause: This was due to an outage at the Azure data center.

Mitigation: A fix has been applied to restore the functionality. End users must pause/resume the dedicated SQL pool and try again.

If this answers your question, please consider accepting the answer by hitting the Accept answer and up-vote as it helps the community look for answers to similar questions.

Share via

Today, all our pipelines that use an external table (dedicated sql pool) failed due to an strange error message.

0 additional answers

Your answer