Compare Azure Data Factory vs. SSIS

What is Azure Data Factory?

Azure Data Factory is a managed cloud-based data integration service. It facilitates the creation, scheduling and monitoring of data pipelines and ETL/ELT workflows. The service builds on the Reliable Services framework, which is built into the Microsoft Azure platform. Azure Data Factory provides a highly available, scalable and secure system for moving data between your on-premises resources, cloud services and big data systems.

What is SSIS?

Microsoft SSIS, a data integration tool first introduced as part of SQL Server 2005, includes a set of transformation components that can be used to perform operations such as data cleansing, aggregation and column mapping. It is designed to help get your data into the right place in an automated fashion while also giving you options to manipulate and cleanse your data along the way.

Azure Data Factory vs. SSIS

Azure Data Factory is suited to organizations looking to migrate workloads from on-premises servers into the cloud. SSIS offers more customization options when designing data pipelines.

Azure Data Factory has built-in support for Azure HDInsight, a managed Hadoop service. This means that the service can be used to process big data sets, something that would be difficult to do with SSIS. Azure Data Factory supports both batch and streaming data processes while SSIS supports only batch processes.

Azure Data Factory allows you to define a series of tasks that need to be performed on data, such as copying data from one location to another, analyzing it and storing it in a database. You provide the definitions and parameters for these tasks at runtime, and Azure Data Factory will figure out how to orchestrate and automate them.

SISS, meanwhile, is an automation tool. It lets you build workflows by dragging and dropping modules like a database mapper or a connector builder. You provide a workflow for processing data, and SISS will automate it.

Azure Data Factory and SSIS differ in terms of the environments they support, data integration and management features, and more.

Support for on-premises and cloud environments

You can use Azure Data Factory as an on-premises tool and move your pipeline off premises. You can even use it in an entirely public cloud environment. Similarly, SSIS can work both on premises and in the Azure public cloud.

Both SSIS and Azure Data Factory can operate in a hybrid data integration environment. In this environment, the source systems are on premises, and the staging area of your pipeline as well as your target system are in Azure.

Data integration and management

Azure Data Factory integrates with a wide range of data sources both on-premises and in the cloud. This factor makes it especially suitable for hybrid data integration scenarios. Azure Data Factory integrates with the following:

• Azure Data Lake Store
• Azure Blob Storage
• Azure SQL Database
• Azure SQL Data Warehouse

It also integrates with these on-premises data sources:

• SQL Server
• Oracle
• Teradata
• MongoDB

Due to the variety of data sources and formats, you can clean and prepare your data for analysis without writing any code. For data analytics, you can integrate Azure Data Factory with other Azure services such as HDInsight and Power BI.

Azure Data Factory provides a set of built-in connectors that you can use to copy data from and to popular data stores without having to write any custom code. You can also use the service to run big data processing tasks such as HDInsight Hive jobs and Azure Databricks Spark jobs. In addition, Azure Data Factory lets you build custom connectors using the Azure Functions or .NET/.NET 6 Core.

SSIS is a Windows-based ETL tool that has been in use for a long time. While it's a popular tool amongst on-premises data professionals, it doesn’t have the same level of cloud integration capabilities as Azure Data Factory. However, SSIS also supports a wide range of connectors, making it easy to connect to different data sources.

User interface

Azure Data Factory features a GUI for designing data processing workflows that can be executed on-premises, in the Azure public cloud or in hybrid cloud scenarios. It also integrates with SQL Server and supports the Python programming language.

SSIS has a graphical interface that lets you drag and drop components to build your data pipelines. SSIS is a GUI-based data integration and ETL tool with support for connectors.

Native SDK support

Azure Data Factory supports automation via PowerShell, but it doesn’t have a native programming SDK. On the contrary, SSIS is a workflow orchestration service that provides support for a programming SDK.

Development tools and plugins

The SSIS development and management tools include SQL Server Data Tools (SSDT) for building Integration Services packages, and SQL Server Management Studio for managing packages in production.

The Azure Data Factory plugin in Visual Studio provides tools and several templates that are integrated with the Solution Explorer and Diagram View in Visual Studio IDE. You can also use Azure Az PowerShell modules to connect and work with Azure Data Factory.

Batch workloads and streaming data

Azure Data Factory supports batch workloads as well as streaming data. Although SSIS does not support streaming workloads, you can use it to move data around.

If your workflow requires both ETL operations and complex transformations that involve many steps with multiple outputs, consider using Azure Data Factory because of its ability to handle these scenarios efficiently.

Support for structured vs. unstructured data

A main difference between Azure Data Factory and SSIS is that the former is used for processing both structured and unstructured data. SSIS is only used for processing structured data. Essentially, Azure Data Factory can be used for tasks such as data cleansing and transformation while SSIS can only be used for data transformation.

Azure Data Factory can automatically detect and parse schema from many common file formats, such as CSV, JSON and Avro. It simplifies working with unstructured data sources that don’t have a predefined schema. SSIS requires you to manually define the schema for each data source. This task can be time consuming and error prone.

Azure Data Factory also offers a built-in transformation called Parse JSON that can be used to extract data from JSON files. This transformation facilitates working with JSON data, even if the structure of the files is complex. Additionally, Azure Data Factory can quickly process large amounts of data, making it ideal for dealing with big data sets. There is no equivalent transformation in SSIS.

Performance

If you measure the performance of Azure Data Factory and SSIS with a small file and no transformation, the results would be almost the same. If you’re using a larger file with or without transformations, Azure Data Factory is much faster than SSIS.

Cost

SSIS is available with a SQL Server license. Azure Data Factory is a pay-as-you-go service. Azure Data Factory will likely save you money in orchestration scenarios because it automates tasks that would otherwise require a team of engineers to perform manually.

Architecture

Azure Data Factory is hosted entirely in the cloud and depends on other Azure services to operate. Azure Data Factory networks are stateless. Each component must take responsibility for keeping track of its identity and any data it needs to perform its task.

SISS is an on-premises tool that runs in your data center. All the processing happens within your data center. This makes scaling easier because you know all the components already exist in a low-latency location.