在 Azure 数据工厂复制活动中参数化截断 Azure SQL 数据库中的表。

huangapple go评论61阅读模式
英文:

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

问题

我遵循了一个关于如何在Azure SQL DB中使用ADF截断表的SO问题。

我试图使用我的复制活动来模拟问题中的示例,如下所示:

我的源细节如下:

在 Azure 数据工厂复制活动中参数化截断 Azure SQL 数据库中的表。

在 Azure 数据工厂复制活动中参数化截断 Azure SQL 数据库中的表。

在 Azure 数据工厂复制活动中参数化截断 Azure SQL 数据库中的表。

在 Azure 数据工厂复制活动中参数化截断 Azure SQL 数据库中的表。

在 Azure 数据工厂复制活动中参数化截断 Azure SQL 数据库中的表。

使用上述配置,我得到以下错误:

{
    "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": []
}

然而,如果我将架构和表名硬编码如下 TRUNCATE TABLE dbo.mytablename,一切都将正常工作。

作为更新,我刚刚尝试了以下参数

TRUNCATE TableName@pipeline().parameters.TableName

但失败了。

英文:

I following an SO question on how to truncate a table in Azure SQL DB with ADF
https://stackoverflow.com/questions/69456500/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:

在 Azure 数据工厂复制活动中参数化截断 Azure SQL 数据库中的表。

在 Azure 数据工厂复制活动中参数化截断 Azure SQL 数据库中的表。

在 Azure 数据工厂复制活动中参数化截断 Azure SQL 数据库中的表。

在 Azure 数据工厂复制活动中参数化截断 Azure SQL 数据库中的表。

在 Azure 数据工厂复制活动中参数化截断 Azure SQL 数据库中的表。

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.

答案1

得分: 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 the pipeline and also you have given it with the 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}

在 Azure 数据工厂复制活动中参数化截断 Azure SQL 数据库中的表。

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

Before Truncate:

在 Azure 数据工厂复制活动中参数化截断 Azure SQL 数据库中的表。

After Truncate:

在 Azure 数据工厂复制活动中参数化截断 Azure SQL 数据库中的表。

英文:

>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}

在 Azure 数据工厂复制活动中参数化截断 Azure SQL 数据库中的表。

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

Before Truncate:

在 Azure 数据工厂复制活动中参数化截断 Azure SQL 数据库中的表。

After Truncate:

在 Azure 数据工厂复制活动中参数化截断 Azure SQL 数据库中的表。

huangapple
  • 本文由 发表于 2023年2月26日 19:37:20
  • 转载请务必保留本文链接:https://go.coder-hub.com/75571709.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定