How to Migrate from SSIS to Azure Data Factory?

Enterprises often have to analyze data in disparate forms stored across different locations. Such data has to be consolidated and stored in a central repository such as a data warehouse. The process of consolidation of data from different sources involves the use of data integration paradigms such as ETL (Extract, Transform, Load) or ELT (Extract, Load, Transform).

For over 15 years, Microsoft SQL Server Integration Services (SSIS) has been the preferred tool for enterprises to perform these operations. However, with the advent of big data, enterprises need more robust solutions to manage data silos, handle increasing data volume and complexity, and deal with security and compliance issues, which can be solved with Azure Data Factory (ADF). In this article, we will explain how to migrate on-premises SSIS jobs to Azure Data Factory.

Is Azure Data Factory A Replacement for SSIS?

SQL Server Integration Services (SSIS) is an on-premises platform that is included in the SQL Server database software. It includes several tools and features for data integration and workflow applications. The platform can be used for automating the maintenance of SQL Server databases, updating multidimensional cube data, and performing ETL operations, among others.

Azure Data Factory is a managed cloud service for orchestrating and operationalizing processes to transform large quantities of raw data into actionable business insights. It is a cloud-based code-free ETL as a service solution that can be used for creating and scheduling data-driven workflows (called pipelines) using data from diverse data stores. So, while Azure Data Factory is primarily a data orchestration tool, SSIS is a data migration and ETL tool.

The two, however, have overlapping features, and SSIS continues to be used for on-premises workloads and hybrid deployments that allow users to leverage storage and analytics services, such as Azure Blob Storage, Azure HDInsight, Azure Synapse Analytics, and Azure Data Lake Storage in the cloud. In many cases, Azure Data Factory is used to supplement SSIS, rather than replace it.

How Do Azure Data Factory vs SSIS Compare?

Although both ADF and SSIS have the essential functionalities expected from standard enterprise ETL tools, there are also significant differences. Let us enlist the similarities and differences in the Azure Data Factory vs SSIS comparison.

Similarities Between Azure Data Factory and SSIS

• SSIS components such as Connection Manager, Source/Destination, Package, and Activity have corresponding components in ADF, which are, Linked Service, Datasets, Pipeline, and Task, respectively.
• Both SSIS and ADF are based on Visual Studio and can use Azure features and tools.
• SSIS and ADF use role-based security and can fire HDInsight clusters, alerts, and scripts.
• Both platforms provide methods for defining dependencies and automate processes.

Differences Between Azure Data Factory and SSIS

• SSIS can be used in on-premises, hybrid, or cloud deployments, ADF is a cloud-native PaaS solution.
• While SSIS is primarily used for ETL for loading data, Azure Data Factory offers distributed query processing capabilities for Extract, Load, and Transform (ELT). This eliminates the need for additional resources to transform data before loading.
• ADF uses pipelines for scheduling, SSIS needs another tool such as SQL Agent or Azure Automation for the process.
• ADF requires no additional infrastructure and is available as a pay-as-you-go service, while SSIS is available only with the on-premises SQL Server.
• While ADF is easier to scale and source control, SSIS supports more data sources and out-of-the-box features.

How to Transfer SSIS Jobs to Azure Data Factory?

ETL workloads on SQL Server Integration Services (SSIS) have to be migrated when databases have to be migrated from on-premises

SQL Server to Azure database services, such as Azure SQL Database or Azure SQL Managed Instance. This can be done using Azure-SSIS Integration Runtime (IR) in Azure Data Factory (ADF). Follow the steps below to migrate SSIS jobs to Azure Data Factory.

• Use the Data Migration Assistant (DMA) to assess SSIS packages to identify migration blockers and informative issues.
• Upgrade to the latest version of SQL Server Management Studio (18.5 or higher).
• Navigate to Object Explorer>SQL Server Agent>Jobs>Migrate SSIS Jobs to ADF.
• Login to your Azure subscription and map the paths of SSIS packages and configuration files to the destination paths that ADF pipelines can access.
• Select the jobs that have to be migrated and make appropriate corresponding changes to the Executed SSIS Package activity.
• Generate and deploy the ARM template of the migrated ADF pipelines to ADF.

Apps4Rent Can Help in Migrating SSIS Workloads to Azure Data Factory

SSIS and Azure Data Factory are powerful ETL tools whose capabilities certainly are not mutually exclusive. While Azure Data Factory is an obvious choice if most, or all of your workload is on the cloud, you can use an ADF SSIS runtime or deploy SSIS on Azure Virtual Machines to take advantage of its richer feature set without compromising on the scalability of the cloud environment.

As a Microsoft CSP with gold competency in Cloud Platform and Cloud Productivity, Apps4Rent can help you identify the right Azure services to move your on-premises workloads to the cloud. Contact our Azure consultants, available 24/7 via phone, chat, and email for managed Azure services to help you migrate on-premises SSIS workloads to SSIS in Azure Data Factory.