Sink ___ has invalid settings for writing to error storage

Question

Sink ___ has invalid settings for writing to error storage

Omar Abdel Bari 61 Microsoft Employee

I have a data flow with a cosmos db collection source and sink. A value is added in the middle after retrieving it with a Join from another cosmos collection. There are asserts items at various points prior to the sink. Anytime I activate Output to Separate File option with the container field filled out this error comes up consistently when I run the pipeline. If

Operation on target AddRegionIdToPortInOrders failed: {"StatusCode":"DFExecutorUserError","Message":"Job failed due to reason: at Sink 'PortInOrdersOutput': Sink PortInOrdersOutput has invalid settings for writing to error storage","Details":""}

I tried:

Leaving container and directory fields blank. Same error.
Adding a directory with the folder (handled both cases where it is existing and absent from storage account). Same error.
Going through a video in the documentation (https://learn.microsoft.com/en-us/azure/data-factory/data-flow-sink#errors) but the UI seems outdated as there is no Error tab present in the sink. The section below shows the modern look but with the same settings I get the same error
Documentation of common error codes (https://learn.microsoft.com/en-us/azure/data-factory/data-flow-troubleshoot-guide) , this one is not specified.

Reproduction Steps
I was easily able to reproduce this by creating a separate data flow (and corresponding pipeline) with just a cosmos DB source, assert and cosmos DB sink. The cosmos DB sink must have Output to Separate File enabled.

The only thing I can guess at this point is maybe it doesn't like the storage account. Are there any particular settings required of the storage account to make it work?

Storage details copied from the portal
Performance
Standard

Replication
Locally-redundant storage (LRS)

Account kind
StorageV2 (general purpose v2)

Provisioning state
Succeeded

Created
8/10/2022, 6:52:47 PM

Blob service
Hierarchical namespace
Disabled

Default access tier
Hot

Blob public access
Enabled

Blob soft delete
Enabled (7 days)

Container soft delete
Enabled (7 days)

Versioning
Disabled

Change feed
Disabled

NFS v3
Disabled

Allow cross-tenant replication
Disabled

HimanshuSinha-msft 19,491 Reputation points Microsoft Employee Moderator

2022-08-15T23:50:55.47+00:00
Hello @Omar Abdel Bari ,
Thanks for the question and using MS Q&A platform.

As we understand the ask here is to get rid of the error "has invalid settings for writing to error storage" , please do let us know if its not accurate.
I am able to repro the issues and have reached to the datafow to get some help on this . I will update you once I hear back from them .

Thanks
Himanshu

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

Want a reminder to come back and check responses? Here is how to subscribe to a notification

If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
Omar Abdel Bari 61 Reputation points Microsoft Employee

2022-08-16T16:57:27.173+00:00

That is correct and thank you!
I have confirmed the issue has nothing to do with the storage account. I switched the sink to Azure SQL with similar settings and experienced no error. So I'm thinking it could be a bug with the CosmosDB sink in particular. Trying to find a work around in the JSON editor to get it working.
Maddy 1 Reputation point

2022-08-25T05:29:45.277+00:00

Has the cause been identified? I am facing the same error msg when trying to direct assert row failures to a json file in blob storage. The sink was working but since configuring the Sink's errors tab to output failures to another file I get the same error in Data preview.
Omar Abdel Bari 61 Reputation points Microsoft Employee

2022-08-31T00:10:26.087+00:00

Hi @Maddy , I have a workaround after considering different options (though I hope they include this soon).

You may make use of Conditional Splits to carry out different assertions (doesn't work for uniques though) and Derived column to add a standard field for each branch which contains your custom error message. You then would need to use a union to include all the errors of that same format and then into one sink. Do not try to add the same sink for each one as this did give me an error (I forgot what the message is but there is a stackover flow post I commented on regarding it).

Here's an example if it helps:

Your answer

HimanshuSinha-msft 19,491 Reputation points Microsoft Employee Moderator

2022-08-15T23:50:55.47+00:00

Hello @Omar Abdel Bari ,
Thanks for the question and using MS Q&A platform.

As we understand the ask here is to get rid of the error "has invalid settings for writing to error storage" , please do let us know if its not accurate.
I am able to repro the issues and have reached to the datafow to get some help on this . I will update you once I hear back from them .

Thanks
Himanshu

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

Want a reminder to come back and check responses? Here is how to subscribe to a notification

If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
Omar Abdel Bari 61 Reputation points Microsoft Employee

2022-08-16T16:57:27.173+00:00

That is correct and thank you!
I have confirmed the issue has nothing to do with the storage account. I switched the sink to Azure SQL with similar settings and experienced no error. So I'm thinking it could be a bug with the CosmosDB sink in particular. Trying to find a work around in the JSON editor to get it working.
Maddy 1 Reputation point

2022-08-25T05:29:45.277+00:00

Has the cause been identified? I am facing the same error msg when trying to direct assert row failures to a json file in blob storage. The sink was working but since configuring the Sink's errors tab to output failures to another file I get the same error in Data preview.
Omar Abdel Bari 61 Reputation points Microsoft Employee

2022-08-31T00:10:26.087+00:00

Hi @Maddy , I have a workaround after considering different options (though I hope they include this soon).

You may make use of Conditional Splits to carry out different assertions (doesn't work for uniques though) and Derived column to add a standard field for each branch which contains your custom error message. You then would need to use a union to include all the errors of that same format and then into one sink. Do not try to add the same sink for each one as this did give me an error (I forgot what the message is but there is a stackover flow post I commented on regarding it).

Here's an example if it helps:

Share via

Sink ___ has invalid settings for writing to error storage

Your answer