CODEWITHSUNDEEP
azure-data-factoryazure-synapse
Mateo Estrada
Mateo Estrada

Reputation: 15

Azure Synapse Copy Activity error: CREATE TABLE dbo is not supported

I am having trouble using the Table Option named Auto Create Table on a Copy Data activity.
The source I use is a REST API which can be requested correctly, and actually data can be previewed normally.
However, even though I count with every permission available (including blob storage contributor), the following message is received after Debug given the settings of Sink section of Copy Data:

{
"errorCode": "2200",
"message": "ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'CREATE TABLE dbo is not supported.',Source=,''Type=System.Data.SqlClient.SqlException,Message=CREATE TABLE dbo is not supported.,Source=.Net SqlClient Data Provider,SqlErrorNumber=15868,Class=16,ErrorCode=-2146232060,State=5,Errors=[{Class=16,Number=15868,State=5,Message=CREATE TABLE dbo is not supported.,},],'",
"failureType": "UserError",
"target": "From API to Data Lake",
"details": []
}

Below I attach screenshots of the Copy Data settings. I use Azure SQL Database as our Sink dataset, and the same for Linked Service. Even though we create a table beforehand, the process does not work. Given that I wish to use the Serverless SQL pool, are there any suggestions or alternatives for this pipeline to work properly?

Azure Synapse copy data config1

Azure Synapse copy data config2

I am trying to make a GET Request to the API I am working with, and to send the data from the generated parquet file to a table using the Copy Data activity, using Azure SQL Database as Sink dataset.

Upvotes: 0

Views: 1260

Answers (2)

Pratik Lad
Pratik Lad

Reputation: 8301

I tried to reproduce your scenario and I got similar error.

enter image description here

The Cause as per Microsoft Document

The Serverless SQL pool has no local storage, only metadata objects are stored in databases. Therefore, T-SQL related to the following concepts isn't supported:

  • Tables
  • Triggers
  • Materialized views
  • DDL statements other than ones related to views and security
  • DML statements

Given that I wish to use the Serverless SQL pool, are there any suggestions or alternatives for this pipeline to work properly?

As we can not create table in Serverless SQL, but we can create external table in serverless SQL. The workaround is to store the data from API in the ADLS of the synapse workspace as csv file and then create external table on it as below:

  • Storing the API data in the ADLS of the synapse workspace as csv file enter image description here

  • Then Go to synapse workspace And Data >> Linked >> select your ADLS account and container >> click on file >> New SQL script >> Create External Table. enter image description here

  • Check all settings and click on continue. enter image description here

  • Give the table name you want and click on Open script it will generate the script. enter image description here

  • Run the script

Output:

enter image description here

Upvotes: 0

AnnuKumari
AnnuKumari

Reputation: 563

Please check if 'dbo' schema exist or not in the database. If it doesn't exist you can create it by using the following query in precopy script of sink settings.

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'dbo') BEGIN EXEC sp_executesql N'CREATE SCHEMA dbo'; END

Upvotes: -1

Related Questions