Migrating SSIS Workloads to Azure Data Factory

The company is a global leader in selling consumer goods online and offline. The company uses SQL Server Integration Services (SSIS) to perform various data integration tasks, such as loading data from various sources, transforming and cleansing data, and loading data into data warehouses and data marts. The company has hundreds of SSIS packages running on multiple on-premises servers, scheduled by SQL Server Agent jobs. 

• The on-premises SSIS servers had limited resources and could not handle the increasing data volume and complexity. 
• The SSIS packages had limited visibility and monitoring, which made it hard to troubleshoot and optimize them. 
• The SSIS packages had dependencies on external components and configurations, such as drivers, connections, and proxies, which made them difficult to migrate and manage.

• Reduce the operational and maintenance costs of the on-premises SSIS servers and licenses. 
• Improve the performance and reliability of the data integration pipelines and reduce the data latency and failures. 
• Minimize the changes and disruptions to the existing SSIS packages and workflows.
  

We decided to migrate its SSIS workloads to Azure Data Factory, a fully managed cloud service for data integration. Azure Data Factory provides the following benefits and advantages for the company: 

• SSDT) 2Azure Data Factory allows the company to provision and scale Azure-SSIS Integration Runtime (IR) clusters on demand, without the need to manage and maintain the underlying infrastructure and licenses. 
• Azure Data Factory supports the migration of SSIS packages to the cloud with minimal changes, using SQL Server Management Studio (SSMS) SSIS Job Migration Wizard1 or Azure-enabled SQL Server Data Tools (SSDT)2. 
• Azure Data Factory enables the company to integrate its SSIS packages with other Azure services, such as Azure Data Lake Storage, Azure Synapse Analytics, and Azure Monitor, to enhance its data integration capabilities and features. 
• Azure Data Factory provides a rich and intuitive user interface for managing, monitoring, and debugging the SSIS packages and pipelines, as well as a REST API and PowerShell cmdlets for automation and orchestration. 

Costing : The company reduced its operational and maintenance costs by 50% by eliminating the need for on-premises SSIS servers and licenses and by paying only for the Azure-SSIS IR clusters when they are running.  

Efficiency : The company improved its performance and reliability by 80% by scaling the Azure-SSIS IR clusters according to the data volume and complexity and by leveraging the high availability and fault tolerance of the Azure Data Factory.  

Collaboration: The company enabled new capabilities and features, such as data lake integration, data quality monitoring, and data lineage tracking, by integrating its SSIS packages with other Azure services, such as Azure Data Lake Storage, Azure Synapse Analytics, and Azure Monitor.  

“The Azure Data Factory portal is a great tool for managing, monitoring, and debugging our SSIS packages and pipelines. We can easily see the data lineage and data quality reports and troubleshoot any issues with the SSIS activities and logs.” Data Analyst