Azure Data Factory - A database operation failed with "Invalid object name"

Question

Hi

I have pipelinesin Data Factory that have been working fine for months, and all started failing today with the following error:

Operation on target [job name] failed: Failure happened on 'Sink' side. 'Type=System.Data.SqlClient.SqlException,Message=Cannot find the object "[table name]" because it does not exist or you do not have permissions.,Source=.Net SqlClient Data Provider,SqlErrorNumber=4701,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=4701,State=1,Message=Cannot find the object "[table name]" because it does not exist or you do not have permissions.,},],'

Note I've replaced my actual table name with [table name] and same for job name.

I started investigating, and found that looking at these pipelines, all connections to required datasets worked. I went into the specific problematic datasets, and found that the connections work, but when I try to preview the data, I get the following error:

Error details

Error code
22301

Details
A database operation failed with the following error: 'Invalid object name 'dbo.table'.' Invalid object name 'dbo.table'., SqlErrorNumber=208,Class=16,State=1, . Activity ID: aa176c06-9c28-48a3-84de-6b45491b7ab9

I first tested the connection for the dataset and it works. Then I opened SQL management studio with the connection credentials and found that the table DOES exist and that I can access and read from it without issues.

Why is it giving me this error on so many of my datasets all of a sudden?

Answer 1

Hello @Cornel Verster and welcome to Microsoft Q&A.

Before I get into trying to help, I noticed a very similar question asked by you:

https://learn.microsoft.com/en-us/answers/questions/466038/index.html

Would it be acceptable to merge these two?

----------

Okay, so the pipelines previously ran fine. You did test connection on datasets and that checked out. Other investigations you made also looked good.
So far there are not many things I can think of to help, but I will try my best. If I cannot help, I offer you a support ticket.

Did the error message show the expected table name and schema?
Did you do a deployment recently? Perhaps something changed the Linked service.
If the Linked service is pointing to a real but incorrect database (like dev / test), I could imagine not finding the table.

Please try using the "preview data" feature in the dataset or copy activity. I find "preview data" to be a much better functionality test than the "test connection", because "test connection" doesn't actually try to get data.

Answer 2

I am experiencing the same.

Answer 3

I am experiencing the same.