Azure数据工厂在一列中映射2列 [英] Azure Data Factory mapping 2 columns in one column

问题描述

有人可以帮助我解决该错误吗?是将文本文件中的两列即first name和last name串联起来，并将这两列合并为我的 Azure SQL数据库中的一个名称列作为 Azure数据工厂中的sink，另一个问题是我想为 male 选择列性别的首字母为M或F和女性分别来自源文本文件，并将其更改为Azure数据工厂管道中我的性别"列中的一个字母M或F 在此处输入图片描述?

Can somebody help me solving the error am getting in concatenating the two columns i.e first name and last name from my text file and merging the two columns into one name column in my Azure SQL database as a sink in Azure Data Factory and another question is that I want to choose the first letter of the column gender that is M or F for male and female respectively from the source text file and changing it to one letter M or F in my gender column in the Azure data factory pipeline enter image description here?

• 更新1

我的表名是[dbo].[联系人]在应用此过程后出现此错误，并且文本文件中我的列名之间存在空格，例如名字和姓氏，这确实造成了问题也是吗?

My table name is [dbo].[Contact] and after applying this procedure am getting this error, and my columns names in the text file has space in between them, like First Name and Last Name, does that create a problem too?

在此处输入图片描述

推荐答案

基于doc:复制活动中的架构映射，架构映射支持合并列.

Based on the doc: Schema mapping in copy activity, merging columns is supported by schema mapping.

作为解决方法，我建议在您的

As workaround , I suggest configure sql server stored procedure in your sql server sink. It can merge the data being copied with existing data.

请按照此文档:

第1步:配置输出数据集:

Step 1: Configure your Output 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].[MarketingType] AS TABLE(
    [FirstName] [varchar](256) NOT NULL,
    [LastName] [varchar](256) NOT NULL,
    [Gender] [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 spOverwriteMarketing @Marketing [dbo].[MarketingType] READONLY
AS
BEGIN
  MERGE [dbo].[jay] AS target
  USING @Marketing AS source
  ON (1=1)
  WHEN NOT MATCHED THEN
      INSERT (name, gender)
      VALUES (source.FirstName + ' ' + source.LastName, UPPER(left(source.Gender,1)));
END

输出屏幕截图:

Output Screenshot:

希望它对您有帮助.

这篇关于Azure数据工厂在一列中映射2列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！