Informatica and PL/SQL Conversion to Azure Data Factory
A Global Research and Advisory Company is in a journey to migrate data from on-premise applications to Azure to maintain and enhance its EDW, reporting and analytics applications in order to provide accurate and timely data to users and ensure data availability for Reporting and BI functions.
01 Problem
Limited capacity to convert legacy ETL code from Informatica jobs and PL/SQL procedures to Azure Data Factory (ADF) on Azure within defined timeframe. Over 800 Informatica jobs to convert to ADF. Over 300 PL/SQL procedures to convert to T-SQL. Need to match Orchestration features and functionality in ADF.
02 Solutions
VSS migration services using proprietary automated ETL Conversion solution and Pl/SQL script analyzer and workflow solution to accelerate migration timeline within budget requirements. Conversion completed over 3 iterations to accelerate time-to-production. Build a custom scheduler for ADF pipelines on Azure to meet the features/functionality provided by DAC for the implementation. VSS utility that reads ARM templates from a directory and provides individual ARM templates for each resource of type pipeline, dataflow or dataset in a folder structure. VSS utility that adds a standard web activity to multiple pipelines by directly updating the pipelines in ADF.
03 Results
1 hour effort savings per ADF pipeline using Bitwise utility for ARM template splitting. 0.5 effort savings per ADF pipeline using Bitwise utility for adding activity to pipelines in ADF. Fully converted and unit tested code delivered with ~40% overall time efficiency.
04 Tools Used
Bitwise ETL Converter
Informatica
PowerCenter
PL/SQL
Azure Data Factory (ADF)
T-SQL