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

Cornel Verster 36 Reputation points


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

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

    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:

    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

    I am experiencing the same.

  3. Zeeshan Aziz 1 Reputation point

    I am experiencing the same.

    0 comments No comments