It can be confusing to choose the right ETL Tool for data integration and data migration processes. Professionals always want to choose the tool that will give them the best possible results. In this blog, we will be discussing two important tools, Azure Data Factory and SQL Server Integration Services, and their differences which will help you decide what goes best with your requirements.
SQL Server Integration Services (SSIS)
SSIS is a flexible and fast data warehouse tool used for extracting, integrating, and transforming data. It is used to execute a wide range of data migration tasks as it provides users with a platform to move data from one source to another in an easier manner providing them with complete control to design ETL according to their requirements. SSIS supports a wide range of data sources like SQL Databases, Oracle Databases, Excel Files, DB2 Databases, etc. It helps you to create solutions without writing a single line of code.
SSIS has four important components:
- Control Flow – It is the brain of the SSIS package. It helps to arrange the order of all the components like containers, tasks etc.
- Data Flow – If Control Flow is the brain, Data Flow is the heart of the SSIS Package. It allows you to extract, transform and then load data into another destination.
- Packages – Packages are the collection of Control and Data Flow. The containers and data flow tasks in control flow and the sources and destinations in the Data flow, altogether are known as a package.
- Parameters – These are special types of variables. They help to ease the process of passing run-time values to SSIS packages.
Azure Data Factory
Azure Data Factory is a cloud based ETL service for data integration and data migrations. It enables users to develop data-driven processes to orchestrate data movement and transformations. Azure Data Factory helps you to create and schedule pipelines that can get data from several different data sources. It is also used to give meaning to big data stored in a storage system by helping in data movement and performing transformations on big-scale data.
ADF has four key components:
- Pipeline – Contains the tasks you want to execute. It defines the complete workflow, like what tasks should be performed and in which order.
- Activity – These are the individual steps inside a pipeline, where each activity performs a single task. They can either be chained or run in a parallel manner. They control the flow inside a pipeline.
- Datasets – Collected data is required as an input for ETL process. These are views that represent databases, files, or folders.
- Linked Services – These are basically the connection strings used to connect data sources and services and to authenticate them.
Azure Data Factory vs SSIS
Both of these tools have similar descriptions as they were created for the same purpose. But to understand their differences, let’s compare them.
Learning Curve
Both tools are easy to learn and to perform simple tasks. But to master any new software, a lot of time and practice is required. SSIS has been around for a long time. It is a mature software with few major changes in the past few years. These slower evolutions make it less of a moving target and so it is easy to learn as compared to Azure Data Factory. Also SSIS is well-documented owing to its age, so this gives it a little advantage over ADF.
ADF, on the other hand, is still evolving. More features and capabilities are yet to be released But if you already have some SSIS knowledge, it is much easier to learn with the help of digital training materials and documentations available.
Data Variety
SSIS deals with structured data, which includes a wide range of databases like SQL server, Oracle, DB2 etc. But it does not integrate with new file formats like JSON, Parquet etc. You can either use third party C# connectors or script components for JSON, RestAPIs, etc.
Azure Data Factory can deal with both structured and unstructured data. It can connect to over 90+ sources, including SQL and No SQL Databases, RestAPIs, SaaS Platforms etc. It supports nearly 20 data warehouse and database destinations in the cloud and on-premises.
Data Velocity
SSIS is a batch processing ETL tool. It performs this by grouping the rows to be processed in batches, then processes each batch and updates each group as processed. For real-time data streams, you can develop custom triggers in SSIS.
ADF supports both Batch and Stream Processing. It can process data based on events happening in storage accounts, such as deletion or arrival of files. It also supports tumbling window triggers where you can pass the start and end time for each time window in your query and it will process and return the data between that interval. In addition to these, ADF also supports batch triggers.
Programmability
SSIS has a programming SDK. It allows developers to write their own code for defining their connection objects, tasks, log providers and transformations. It features a programmable object model that allows developers to create, store, and load packages using BIML, as well as create, destroy, and modify any of the contained objects.
On the other hand, ADF does not have a native programming SDK, but it has automation using PowerShell without involving any third-party components. To run your pipelines manually, you can also use methods like .NET SDK, RestAPIs, Python SDKs.
Pricing
SSIS comes as a part of SQL Server License. The pricing is free for Express and Developer editions but for Enterprise, it costs $14,256 per core.
Azure Data Factory provides services at pay-as-you-go pricing. It is calculated based on the number of pipeline orchestration runs, data flow execution and debugging, and the number of data factory operations, such as pipeline monitoring.
Pros and Cons
SQL Server Integration Service
Pros
- Able to handle data from a variety of data sources.
- Provides transformation functionality
- C# or VBA can be used to extend its functionality
- Easy to learn and user-friendly
- Debugging capabilities are great, particularly during flow execution.
Cons
- Package execution report can only be seen through Management Studio.
- Running multiple packages in parallel is difficult. Slow Evolution
- Working with unstructured datasets might be difficult.
Azure Data Factory
Pros
- Provides a server less solution eliminating the mundane tasks of maintenance and update of software.
- Supports integration with multiple 3rd party connectors.
- Supports long and time-consuming queries.
- Highly scalable and cost effective
- Creating pipelines schedules is much easier
Cons
- Less native transform functions as compared to SSIS.
- Does not have intelligence or debugging tools
- Needs to maintain perfect billing strategy or else will lead to excessive cost.
- Lack of flexibility as compared to other ETL tools, e.g. C# script component.
Conclusion
SSIS and ADF both are highly capable ETL tools. Each one can succeed when used properly. If your workload is mostly on-prem or ETL processes are running consistently through out the day, then SSIS would work well for you. But if you only want to only pay for the resources used and most of your workload is in the cloud then ADF is a suitable choice for you. Considering the factors discussed above and what is required by your project, you can decide which is the right tool for the job whether SSIS, ADF, or a hybrid of the two.