Azure Data Factory Copy Activity fails to auto create table when copying from REST source to Azure SQL database

Tina Eilert(ARM) 0

I am using a Copy Activity in Azure Data Factory. I have a REST source/data set created and a Azure SQL Database sink/data set created. I have the Copy Activity set to auto create and the table does not exist in the database before running the pipeline containing the Copy Activity. I have manually added the schema (dbo) and table name (user) in the sink data set and have Insert selected as the Write Behavior. I have not imported a schema on the Schema tab of this data set. I have also not entered any information in the Mapping tab on the Copy Activity. I have Interactive Authoring enabled because everything is in the cloud (no on prem).

When I run the pipeline with the Copy Activity, I receive the following error message after a couple of minutes:

Operation on target Create and Copy failed: Failure happened on 'Sink' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed. Please search error to get more details.,Source=Microsoft.DataTransfer.Connectors.MSSQL,''Type=System.InvalidOperationException,Message=Cannot access destination table '[dbo].[user]'.,Source=Microsoft.Data.SqlClient,''Type=Microsoft.Data.SqlClient.SqlException,Message=Invalid object name 'dbo.user'.,Source=Framework Microsoft SqlClient Data Provider,'

I am expecting auto create to create the table in the database when the pipeline runs. Instead I am receiving an error stating the table (that hasn't been added with auto create yet) cannot be accessed. It is as if the activity is expecting the table to already exist even though I have the activity set to auto create.

Please let me know if you need more information from me. Thank you!

Ganesh Gurram 4,335 Reputation points Microsoft Vendor

2025-02-20T20:53:01.03+00:00

Hi @Tina Eilert(ARM)

Thank you for posting your query!

When using Azure Data Factory's Copy Activity to auto-create tables, there are several factors to consider that could cause the operation to fail.

Here's a checklist and some troubleshooting steps to help resolve the issue:

Check Permissions - Ensure that the user account used by Azure Data Factory to connect to the Azure SQL Database has the necessary permissions to create tables. The account should have at least db_ddladmin or db_owner privileges on the target database.

Inspect Linked Service Configuration - Verify that the linked service for the Azure SQL Database is correctly configured with the right connection string and authentication details.

REST Source Schema - Make sure that the REST source returns data in a structured format that can be inferred into a table schema. JSON or XML are typically used, and they should contain clear, consistent structures.

Data Types - Sometimes, automatic schema inference may fail if the data types from the REST source cannot be mapped correctly to SQL Server data types. Check the data returned by the REST API to ensure it can be translated into SQL-compatible types.

Azure Data Factory Version - Ensure you are using a version of Azure Data Factory that supports the auto-create feature, as older versions might not support this functionality or might have known issues.

Review Error Logs - The error message suggests that the operation failed due to an "Invalid object name 'dbo.user'." This might indicate that the table wasn't created due to a permissions issue or a misconfiguration. Check the logs for more detailed error messages that might give additional context.

Hope this helps. Do let us know if you have any further queries.
Ganesh Gurram 4,335 Reputation points Microsoft Vendor

2025-02-21T14:41:48.7466667+00:00

@Tina Eilert(ARM) - We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

Azure Data Factory Copy Activity fails to auto create table when copying from REST source to Azure SQL database

Your answer