Azure数据工厂复制活动将字符串(从csv)映射到Azure SQL表接收器uniqueidentifier字段失败 [英] Azure Data factory copy activity failed mapping strings (from csv) to Azure SQL table sink uniqueidentifier field

问题描述

我有一个包含复制活动的Azure数据工厂(DF)管道.复制活动使用HTTP连接器作为源来调用REST端点，并返回沉入Azure SQL数据库表的csv流.

I have an Azure data factory (DF) pipeline that consists a Copy activity. The Copy activity uses HTTP connector as source to invoke a REST end-point and returns csv stream that sinks with Azure SQL Database table.

当CSV包含映射到目标表中带有错误消息The given value of type String from the data source cannot be converted to type uniqueidentifier of the specified target column的uniqueIdentifier字段的字符串(例如40f52caf-e616-4321-8ea3-12ea3cbc54e9)时，复制失败.

The Copy fails when CSV contains strings (such as 40f52caf-e616-4321-8ea3-12ea3cbc54e9) which are mapped to an uniqueIdentifier field in target table with error message The given value of type String from the data source cannot be converted to type uniqueidentifier of the specified target column.

我试图用{}包裹源字符串，例如{40f52caf-e616-4321-8ea3-12ea3cbc54e9}，但没有成功.

I have tried to wrapped the source string with {} such as {40f52caf-e616-4321-8ea3-12ea3cbc54e9} with no success.

如果我将目标表字段从uniqueIdentifier修改为nvarchar(100)，则复制"活动将起作用.

The Copy activity will work if I modified the target table field from uniqueIdentifier to nvarchar(100).

推荐答案

我会重现您的问题.

原因是源和接收器的数据类型不匹配.您可以检查 SQL Server的数据类型映射.

The reason is data types of source and sink are dismatch.You could check the Data type mapping for SQL server.

您的源数据类型是string，它映射到nvarchar或varchar，并且sql数据库中的uniqueidentifier在天蓝色数据工厂中需要GUID类型.

Your source data type is string which is mapped to nvarchar or varchar, and uniqueidentifier in sql database needs GUID type in azure data factory.

因此，请在sql服务器接收器中配置sql server stored procedure作为一种解决方法.

So,please configure sql server stored procedure in your sql server sink as a workaround.

请按照此文档:

第1步:配置接收器数据集:

Step 1: Configure your Sink dataset:

步骤2:在复制活动中配置接收器部分，如下所示:

Step 2: Configure Sink section in copy activity as follows:

第3步:在数据库中，定义与sqlWriterTableType相同名称的表类型.请注意，表类型的架构应与输入数据返回的架构相同.

Step 3: In your database, define the table type with the same name as sqlWriterTableType. Notice that the schema of the table type should be same as the schema returned by your input data.

    CREATE TYPE [dbo].[CsvType] AS TABLE(
    [ID] [varchar](256) NOT NULL
)

步骤4:在数据库中，使用与SqlWriterStoredProcedureName相同的名称定义存储过程.它处理来自指定源的输入数据，并合并到输出表中.请注意，存储过程的参数名称应与数据集中定义的"tableName"相同.

Step 4: In your database, define the stored procedure with the same name as SqlWriterStoredProcedureName. It handles input data from your specified source, and merge into the output table. Notice that the parameter name of the stored procedure should be the same as the "tableName" defined in dataset.

Create PROCEDURE convertCsv @ctest [dbo].[CsvType] READONLY
AS
BEGIN
  MERGE [dbo].[adf] AS target
  USING @ctest AS source
  ON (1=1)
  WHEN NOT MATCHED THEN
      INSERT (id)
      VALUES (convert(uniqueidentifier,source.ID));
END

输出:

Output:

希望它对您有帮助.任何关注，请随时让我知道.

Hope it helps you.Any concern,please free feel to let me know.

这篇关于Azure数据工厂复制活动将字符串(从csv)映射到Azure SQL表接收器uniqueidentifier字段失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！