How Parameterize Truncating a table in Azure SQL DB within an Azure Data Factory Copy Activity

Question

I following an SO question on how to truncate a table in Azure SQL DB with ADF how to replace data in azure sql database using azure data factory?

I am trying to emulate the sample in the question using my Copy Activity as follows:

My Source details are as follows:

With the above configuration I get the following error:

{
    "errorCode": "2200",
    "message": "ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Cannot find the object \"targettable\" because it does not exist or you do not have permissions.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Cannot find the object \"targettable\" 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 \"targettable\" because it does not exist or you do not have permissions.,},],'",
    "failureType": "UserError",
    "target": "Copy From CRM to SQLDB",
    "details": []
}

However, if I were to hardcode the schema and table name as follows TRUNCATE TABLE dbo.mytablename everything would work fine.

By way of an update, I just tried with the following parameters

TRUNCATE TableName@pipeline().parameters.TableName

And it failed.

did you try TRUNCATE table @{pipeline().parameters.TableName}? — Rakesh Govindula
– Rakesh Govindula, Commented Feb 26, 2023 at 11:56
Hi @RakeshGovindula, I haven't tried that. I will try that right now .. thanks for reaching out. — Patterson
– Patterson, Commented Feb 26, 2023 at 12:06

Rakesh Govindula · Accepted Answer · 2023-02-26 13:28:29Z

1

Message=A database operation failed with the following error: 'Cannot find the object \"targettable\" because it does not exist or you do not have permissions.

This error occurred because of this query TRUNCATE TABLE dbo.targettable.

Here targettable is a SQL database parameter and we cannot access its value inside pipeline and also you have given it with query.
It made the meaning of SQL query as to truncate the table named targettable which is not available in the database and that's why it gave you the above error.

To resolve it give the correct parameter in the query with string interpolation like below.

TRUNCATE table @{pipeline().parameters.TableName}

enter image description here

I have given the pipeline parameter as sample1 which is my table name.

Before Truncate:

enter image description here

After Truncate:

enter image description here

answered Feb 26, 2023 at 13:28

Rakesh Govindula

11.9k2 gold badges5 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How Parameterize Truncating a table in Azure SQL DB within an Azure Data Factory Copy Activity

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related