Azure Data Factory mapping 2 columns in one column

Question

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?

Update 1

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?

enter image description here

Please refer to my update answer and follow my steps to configure stored procedure in your database.Any concern, please let me know. Just add comment in the latter of my answer. — Jay Gong
I marked already I need more reputations to reflect on the vote, I did vote for you, and thanks for all the help. — sxb1649

Jay Gong Jay Gong · Accepted Answer · 2018-07-16T03:38:18

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.

Please follow the steps from this doc:

Step 1: Configure your Output dataset:

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

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
)

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:

Hope it helps you.

Azure Data Factory mapping 2 columns in one column

1 Answers