Azure Data Factory fails to execute copy data task to SQL Server

0
votes

I have an Azure Data Factory pipeline that contains copy data task. The task copies the data from Azure Blob storage to SQL Server Table. The SQL Server is hosted on-prem and hence is accessed through integration runtime.

I tested the connections to both Azure Blob Storage and SQL Server from Azure Data Factory and they work.

However, when I try to execute the copy task I get the following error:

enter image description here

The object (covered in black for confidentiality) is the name of the table I want to write to in SQL Server. I can confirm that the table exists. I can confirm that the user that is the integration run time is using to access SQL server is having the required permissions to write to the table.

2
sql-serverazureazure-data-factory
As per the question guide, please DO NOT post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. - Dale K

2 Answers

1
votes

This is not an issue with Data factory, as you say your connection is successful it seems to be true.

It more of seems to an issue with either not using the correct database or you do clearly have no access on the table.

Can you use the same database credentials login using SSMS and try running select/update queries?

Also can you check if the auto increment is true?

Please find this link for your reference - > https://support.microsoft.com/en-us/topic/error-message-when-you-try-to-insert-data-into-a-custom-table-in-microsoft-dynamics-nav-cannot-find-the-object-navdbname-dbo-companyname$-tablename-because-it-does-not-exist-or-you-do-not-have-permissions-70dc2a61-c5f7-a85e-ae24-0b4d8931d9ef

0
votes

A few things you can do to further check if the table is accessible.

  1. Open the related Dataset that is used as 'Sink'
  2. In Connection tab of the dataset, try both 'Test connection' and 'Preview data', as in the image below. image1
  3. In Schema tab, try the 'Import schema' button, as in the image below. image2