Dynamic Column mapping in Copy Activity in Azure Data Factory
Connect with me on Linkedin
/ dataengg
Please follow the below video
• 21. Dynamic Column mapping in Copy Activit...
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]