Getting strange error when reading cosmosDB container if it is empty from data factory source transform

Question

I am reading the data from cosmos db in azure synapse pipelines in data flow in source tranform.

I can read the data successfully if container has some data. If it is empty I am getting below error.

message":"Job failed due to reason: Cannot resolve column name \"_ts\"

Any one know how to resolve it?

Thanks a lot for the help in advance.

Answer 1

Hello @Anonymous ,

After further research, I found out that this is expected behavior.

When you don't have any data in your container(no items), there are no columns mapped. You need at least one item in your container for your requirement to work.

As a workaround, you can implement a logic something below to succeed in the pipeline run.

Add an aggregate function to count the number of records and then use a conditional split to route the data to your sink.

Aggregate function expression:

Conditional split:

I was able to run the pipeline successfully with this approach

I hope this helps. Please let me know if you have any further questions.

------------------------------

Please don't forget to Accept Answer if the response helped

Answer 2

I had a similar problem with the below issue.

I was able to solve this by changing the Source Options Input Container into the below Query:

SELECT * FROM c WHERE NOT IS_DEFINED(c._ts) OR c._ts != null

It resolved the issue for the

message": "Job failed due to reason: Cannot resolve column name "_ts"

and run the pipeline successfully!

I hope this helps out and don't forget to Accept this answer

Answer 3

I was able to solve it by manually including the system columns I needed to my cosmosdb dataset, unchecking the checkbox 'Include system columns' and specifying a query in stead of using the container as source.