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

Cornel Verster 36 Reputation points
2021-07-07T09:52:52.143+00:00

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?

Azure SQL Database
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,468 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. MartinJaffer-MSFT 26,011 Reputation points
    2021-07-07T18:39:50.453+00:00

    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.


  2. Tamilselvi Rajendran 1 Reputation point
    2022-10-31T11:50:11.27+00:00

    I am experiencing the same.


  3. Zeeshan Aziz 1 Reputation point
    2023-04-06T09:37:22.81+00:00

    I am experiencing the same.

    0 comments No comments