SQL Server Integration Services
In this section we will look at SQL Server Integration Services.
What is it?
SQL Server Integration Services (SSIS) is a component with SQL Server which is used to implement Extract Transform Load (ETL) solutions.
SSIS includes the following key features:
- Connectors to connect to many systems
- Tasks for processing data
- Tasks for transforming data
- Graphical interface to build your ETL process
- Connectivity for Azure
The strength of SSIS is the ability to rapidly design an graphical ETL process which can be production ready without writing a line of code (in lots of cases).
Other strengths include:
- Ability to manage the package and configuration
- Support fo for Visual Studio
SSIS is a mature project which has been around for years so there are not many weaknesses but tolist a few:
- SSIS was not natively designed for Cloud applications and some ETL scenarios might better suit Data Factory
- Some moden workloads for ETL might meed an exceptional scale tool, there may be other options for moving large scale data around.
SSIS has a dependancy on a SQL Server license
SSIS runs on a Windows machine or VM that you have installed these components of SQL Server on. SSIS is a subset of the SQL Server installation.
To run SSIS in production you need to host/run the package on a machine which is covered by a SQL Server license.
Azure Data Factory
Azure Data Factory offers a platform as a service alternative to SSIS which has some feature overlap for certain scenarios. Data Factory also offers the ability to run SSIS packages on one of its runtime host modules which is specifically designed for SSIS support.
SSIS is mature, massively used by customers who have relied on it for years. SSIS follows the SQL Server lifecycle and is a safe technology for years to come but we believe that new and existing customers should consider a hybrid model of Data Factory and SSIS to take advantage of each products benefits and to also be aligned with any future investments from Microsoft.
Microsoft have also offered the Self Hosted Runtime for Data Factory which will allow you to run SSIS packages from Azure Data Factory providing an aligned migration from SSIS to ADF but with fully support for your existing investments.