Dynamic Column mapping in Copy Activity in Azure Data Factory

Connect with me on Linkedin
/ dataengg
Please follow the below video
• 21. Dynamic Column map...
Scripts used in the demo
/****** Object: Table [dbo].[Movie] Script Date: 3/2/2022 1:15:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP TABLE [dbo].[moviesDB1]
GO
DROP TABLE [dbo].[moviesDB2]
GO
CREATE TABLE [dbo].[moviesDB1](
[movie] [int] NULL,
[title] [varchar](1000) NULL,
[genres] [varchar](1000) NULL,
[year] [int] NULL,
[Rating] [int] NULL,
[RottonTomato] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[moviesDB2](
[movie] [int] NULL,
[title] [varchar](1000) NULL,
[genres] [varchar](1000) NULL,
[year] [int] NULL,
[Rating] [int] NULL,
[RottonTomato] [int] NULL,
[MassRating] [int] NULL
) ON [PRIMARY]
GO
CREATE SCHEMA cntl
truncate table cntl.FileMapping
CREATE TABLE cntl.FileMapping (id int, SourceFileName varchar(100), TargetTableName varchar(100) )
INSERT INTO cntl.FileMapping values (1, 'moviesDB1.csv','moviesDB1')
INSERT INTO cntl.FileMapping values (2, 'moviesDB2.csv','moviesDB2')
CREATE TABLE cntl.ColumnsMapping
(id INT,
SourceFileName VARCHAR(100),
SourceAttributeName VARCHAR(100),
TargetTableName VARCHAR(100),
TargetAttributeName VARCHAR(100),
IsActive BIT
);
INSERT INTO cntl.ColumnsMapping values (1, 'moviesDB1.csv','movie','dbo.moviesDB1','movie',1)
INSERT INTO cntl.ColumnsMapping values (2, 'moviesDB1.csv','title','dbo.moviesDB1','title',1)
INSERT INTO cntl.ColumnsMapping values (3, 'moviesDB1.csv','genres','dbo.moviesDB1','genres',1)
INSERT INTO cntl.ColumnsMapping values (4, 'moviesDB1.csv','yr','dbo.moviesDB1','yr',1)
INSERT INTO cntl.ColumnsMapping values (5, 'moviesDB1.csv','Rating','dbo.moviesDB1','Rating',1)
INSERT INTO cntl.ColumnsMapping values (6, 'moviesDB1.csv','Rotton Tomato','dbo.moviesDB1','RottonTomato',1)
INSERT INTO cntl.ColumnsMapping values (1, 'moviesDB2.csv','movie','dbo.moviesDB2','movie',1)
INSERT INTO cntl.ColumnsMapping values (2, 'moviesDB2.csv','title','dbo.moviesDB2','title',1)
INSERT INTO cntl.ColumnsMapping values (3, 'moviesDB2.csv','genres','dbo.moviesDB2','genres',1)
INSERT INTO cntl.ColumnsMapping values (4, 'moviesDB2.csv','yr','dbo.moviesDB2','yr',1)
INSERT INTO cntl.ColumnsMapping values (5, 'moviesDB2.csv','Rating','dbo.moviesDB2','Rating',1)
INSERT INTO cntl.ColumnsMapping values (6, 'moviesDB2.csv','Rotton Tomato','dbo.moviesDB2','RottonTomato',1)
INSERT INTO cntl.ColumnsMapping values (7, 'moviesDB2.csv','MassRating','dbo.moviesDB2','MassRating',1)
delete from cntl.ColumnsMapping where SourceAttributeName='RottonTomato'
---------- Generate dynamic json mapping -----------------------------------------------------------
exec cntl.sp_returnColumns 'moviesDB1.csv'
ALTER PROC cntl.sp_returnColumns
@FileName Varchar(100)
as
SELECT
-- '"translator": {' +
'{"type": "TabularTranslator", "mappings": [ '
+ string_agg(
'{"source":{ "name":"' + em.SourceAttributeName + '"},"sink":{"name":"' + em.TargetAttributeName + '"}}',
','
) + ' ] } '
--+
--' "typeConversion": true,
-- "typeConversionSettings": {
-- "allowDataTruncation": true,
-- "treatBooleanAsNumber": false
-- }
-- }'
AS ColumnMapping
FROM cntl.ColumnsMapping em where em.IsActive = 1
and em.SourceFileName = @FileName
-------- Update isactive = 0 -----
update cntl.ColumnsMapping set IsActive = 1 where TargetAttributeName='RottonTomato'
update cntl.ColumnsMapping set SourceAttributeName='year' where SourceAttributeName='yr'
update cntl.ColumnsMapping set TargetAttributeName='year' where TargetAttributeName='yr'
TRUNCATE TABLE [dbo].[moviesDB2]
TRUNCATE TABLE [dbo].[moviesDB1]

[@karthikvalluri8125]

Excellent demo lokesh!!

[@SQL4ALL]

Thanks brother

[@GreatIndia1729]

Super :)

[@sabasehreen5326]

Suppose we have 3 column in source file and in table we have 4 column in the sink. And we want to do one to one mapping for that and the addition column in sink we should get it as NULL while mapping it is possible?if yes.. Can you say the steps for that.

[@SQL4ALL]

well, the steps would be the same as described in the video. For columns not present in Source, source metadata values will be null and is active flag will be set to 0

[@RimBroo]

What if i dont know how the tables looks like? meaning i dont know the column names or how many column there is in every table that I have. I have 23 tables in total..

[@SQL4ALL]

Why would you don't know, you may be query sys.colums table to construct a new metadata column

[@sabareetham.premnath2732]

What to do, if we want datatype to be matched in copy activity

[@SQL4ALL]

Match the data type? Could you please elaborate your question ❓

[@prajvalsingh810]

The process will be same for excel to sql dynamic column mapping or it'll be different ?

[@SQL4ALL]

Should be the same. Test it out

[@ezra1562]

🅿🆁🅾🅼🅾🆂🅼