Azure Data Factory合并操作失败 [英] Azure Data Factory Merging operation failed

问题描述

HI All，



我正在使用Azure数据工厂复制活动来合并从源表到目标表的数据，并使用以下存储过程来合并数据并且它无法正常工作。有人可以帮我理解这里的问题。



1）我在目标表中创建了具有与源相同模式的表类型。



/ ******对象：  UserDefinedTableType [dbo]。[Employee_Type]   脚本日期：5/17/2018 10:37:36 AM ****** /
$
CREATE TYPE [Employees_Type] AS TABLE（

[ID] [int] NOT NULL，

[FirstName] [nvarchar] （50）NULL，

[LastName] [nvarchar]（50）NULL，

[Amount] int NULL，

[ODS_SourceVersion] [bigint] NOT NULL，

[ODS_SourceOperation] [nchar]（1）NOT NULL，

[ODS_IsDeleted] [bit] NOT NULL

）

GO



2）然后使用以下存储过程合并数据。



/ ******对象：  StoredProcedure [dbo]。[sp_UpSert_Employees]   脚本日期：5/17/2018 10:20:29 AM ****** /
$
SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO



CREATE PROCEDURE [dbo]。[sp_UpSert_Employees] @Employees Employees_Type READONLY

AS



MERGE dbo.Employees AS TARGET

使用@Employees作为来源

ON（

TARGET。[ID] = SOURCE。[ID]

）

当匹配时

UPDATE SET

TARGET。[ID] = SOURCE。[ID]

，TARGET。[FirstName] = SOURCE。[FirstName]

，TARGET。[LastName] = SOURCE。[LastName]

，TARGET。[Amount] = SOURCE。[金额]

，TARGET。[ODS_SourceVersion] = SOURCE。[ODS_SourceVersion]

，TARGET。[ODS_SourceOperation] =来源。[ODS_SourceOperation]

，TARGET。[ODS_IsDeleted] = SOURCE。[ODS_IsDeleted]

WHEN NOT与目标比赛然后是
INSERT（

      [ID] 

，[FirstName] 

，[LastName] 

，[金额]

，[ODS_SourceVersion]

，[ODS_SourceOperation]

，[ODS_IsDeleted]

）

VALUES（

  SOURCE。[ID] 

,SOURCE.[FirstName] 

，SOURCE。[LastName] 

，SOURCE。[金额]

，SOURCE。 [ODS_SourceVersion]

，SOURCE。[ODS_SourceOperation]

，SOURCE。[ODS_IsDeleted]

）;



但是没有跟随这里有错误。



{

    " errorCode"：" 2200"，&
    " message"：" ErrorCode = FailedDbOperation，'Type = Microsoft.DataTransfer.Common.Shared.HybridDeliveryException，Message =数据库操作失败，并出现以下错误：&'过程\" sp_UpSert_Employees \"有
没有参数名为\" @ [dbo]。[Employees] \"。& apos;，Source =，''Type = System.Data.SqlClient.SqlException，Message = The procedure \\ \\" sp_UpSert_Employees\"没有参数名为\" @ [dbo]。[Employees] \"。，Source = .Net SqlClient Data Provider，SqlErrorNumber = 349，Class = 16，ErrorCode = -2146232060，State = 1，Errors = [ {Class = 16，Number = 349，State = 1，Message =
procedure \" sp_UpSert_Employees \"没有名为\" @ [dbo]的参数。[Employees] \"。，}，]，'"，

    "failureType"："UserError"，"
    " target"：" IncrementalLoad"

}

解决方案

好问题？  ADF v2是否还支持READONLY参数？

您如何在管道中填充TVP？

如果ADF v2还不支持READONLY参数，那么您唯一的解决方案就是使用自定义.Net组件并在ADO.Net中传递数据集。

HI All,

I am using Azure Data Factory Copy Activity to Merge the data from source to target table and using following stored procedure to merge the data and it's not working. Could some one help me to understand the issue here.

1) I created the table type in target table having same schema as source.

/****** Object:  UserDefinedTableType [dbo].[Employee_Type]    Script Date: 5/17/2018 10:37:36 AM ******/
CREATE TYPE [Employees_Type] AS TABLE(
[ID] [int] NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Amount] int NULL,
[ODS_SourceVersion] [bigint] NOT NULL,
[ODS_SourceOperation] [nchar](1) NOT NULL,
[ODS_IsDeleted] [bit] NOT NULL
)
GO

2) Then using following stored procedure to merge the data.

/****** Object:  StoredProcedure [dbo].[sp_UpSert_Employees]    Script Date: 5/17/2018 10:20:29 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_UpSert_Employees] @Employees Employees_Type READONLY
AS

MERGE dbo.Employees AS TARGET
USING @Employees AS SOURCE
ON (
TARGET.[ID] = SOURCE.[ID]
)
WHEN MATCHED THEN
UPDATE SET
TARGET.[ID] = SOURCE.[ID]
,TARGET.[FirstName] = SOURCE.[FirstName]
,TARGET.[LastName] = SOURCE.[LastName]
,TARGET.[Amount] = SOURCE.[Amount]
,TARGET.[ODS_SourceVersion] = SOURCE.[ODS_SourceVersion]
,TARGET.[ODS_SourceOperation]= SOURCE.[ODS_SourceOperation]
,TARGET.[ODS_IsDeleted] = SOURCE.[ODS_IsDeleted]
WHEN NOT MATCHED BY TARGET THEN
INSERT (
      [ID] 
,[FirstName] 
,[LastName] 
,[Amount]
,[ODS_SourceVersion]
,[ODS_SourceOperation]
,[ODS_IsDeleted]
)
VALUES (
  SOURCE.[ID] 
,SOURCE.[FirstName] 
,SOURCE.[LastName] 
,SOURCE.[Amount]
,SOURCE.[ODS_SourceVersion]
,SOURCE.[ODS_SourceOperation]
,SOURCE.[ODS_IsDeleted]
);

but it is failing with following error here.

{
    "errorCode": "2200",
    "message": "ErrorCode=FailedDbOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'The procedure \"sp_UpSert_Employees\" has no parameter named \"@[dbo].[Employees]\".',Source=,''Type=System.Data.SqlClient.SqlException,Message=The procedure \"sp_UpSert_Employees\" has no parameter named \"@[dbo].[Employees]\".,Source=.Net SqlClient Data Provider,SqlErrorNumber=349,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=349,State=1,Message=The procedure \"sp_UpSert_Employees\" has no parameter named \"@[dbo].[Employees]\".,},],'",
    "failureType": "UserError",
    "target": "IncrementalLoad"
}

解决方案

Good question?  Does ADF v2 support READONLY parameters yet?

How are you populating your TVP in the pipeline?

If ADF v2 doesn't yet support READONLY parameters then maybe your only solution is to use a custom .Net component and pass the dataset in ADO.Net.

这篇关于Azure Data Factory合并操作失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！