End to End Data Migration ETL Framework with Azure Data Factory & T SQL
End to End Data Migration ETL Framework with Azure Data Factory & T SQL
create table ETLMeta.SourceSystemMetaData
(
SNO int identity(1,1),
sourceSystemId int,
sourceType nvarchar(500),
SourceFilePah nvarchar(500),
SourceFileName nvarchar(500),
sourceSystemName nvarchar(50),
sourceSchema nvarchar(100),
sourceTable nvarchar(100),
targetSchema nvarchar(100),
targetTable nvarchar(100)
)
go
insert into ETLMeta.SourceSystemMetaData
(sourceSystemId,sourceType,sourceSystemName,sourceSchema,sourceTable,targetSchema,targetTable)
values
(101, 'table', 'Sales', 'Sales', 'product', 'Landing', 'product')
go
insert into ETLMeta.SourceSystemMetaData
(sourceSystemId,sourceType,sourceSystemName,sourceSchema,sourceTable,targetSchema,targetTable)
values
(101, 'table', 'Sales', 'Sales', 'Sales', 'Landing', 'Sales')
create table ETLAudit.PipelineRunStatus
(
sourceSystemId int,
LoadType nvarchar(500),
sourceSystemName nvarchar(500),
SourceType nvarchar(500),
SourceFIlePath nvarchar(500),
SourceFIleName nvarchar(500),
sourceSchema nvarchar(500),
sourceTable nvarchar(500),
targetSchema nvarchar(500),
targetTable nvarchar(500),
LoadStartDate nvarchar(500),
LoadEndDate nvarchar(500),
Status nvarchar(500),
processRunID nvarchar(500),
PipelineName nvarchar(500)
)
-- Pipeline run status track
alter proc ETLAudit.usp_PipelineRunStatus
(
@SourceSystemID int,
@sourceType nvarchar(100),
@LoadType nvarchar(100),
@SourceSystemName nvarchar(100),
@SourceSchema nvarchar(100),
@SourceTable nvarchar(100),
@TargetSchema nvarchar(100),
@TargetTable nvarchar(100),
@LoadStartDate datetime2,
@LoadEndDate datetime2,
@Status nvarchar(100),
@processRunID nvarchar(1000),
@PipelineName nvarchar(1000)
)
as
begin
insert into ETLAudit.PipelineRunStatus
(sourceSystemId,sourceType,LoadType,sourceSystemName,sourceSchema,sourceTable,targetSchema,targetTable,LoadStartDate,LoadEndDate,Status,processRunID,PipelineName)
values
(@SourceSystemID, @sourceType, @LoadType, @SourceSystemName, @SourceSchema, @SourceTable, @TargetSchema, @TargetTable, @LoadStartDate, @LoadEndDate, @Status, @processRunID, @PipelineName)
end
--Pipeline Run Status Update
alter proc ETLAudit.usp_PipelineRunStatus_update
(
@SourceSystemID int,
@TargetTable nvarchar(100),
@SourceTable nvarchar(100),
@LoadEndDate datetime2,
@Status nvarchar(100),
@processRunID nvarchar(1000)
)
as
set nocount on
begin
update ETLAudit.PipelineRunStatus set LoadEndDate=@LoadEndDate , Status=@Status
where processRunID=@processRunID and sourceSystemId=@SourceSystemID and sourceTable=@SourceTable
end
select *
from ETLMeta.CurationETLTransformations
-----------------------
--drop table ETLMeta.CurationETLTransformations
create table ETLMeta.CurationETLTransformations
(
SNO int identity(1,1),
sourceSystemId int,
sourceSchema nvarchar(100),
sourceTable nvarchar(100),
targetSchema nvarchar(100),
targetTable nvarchar(100),
PrimaryKeyColumn nvarchar(100),
TransformationQuery nvarchar(max)
)
insert into ETLMeta.CurationETLTransformations
(sourceSystemId ,
sourceTable,
sourceSchema,
targetTable,
targetSchema
)
values
(101, 'product', 'landing', 'product', 'curation')
insert into ETLMeta.CurationETLTransformations
(sourceSystemId ,
sourceSchema,
sourceTable,
targetSchema,
targetTable
)
values
(101, 'landing', 'sales', 'curation', 'sales')