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

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')