SQL Server Integration Services
  • 07 Sep 2020
  • 1 Minute To Read
  • Contributors
  • Print
  • Comments
  • Share
  • Dark
    Light

SQL Server Integration Services

  • Print
  • Comments
  • Share
  • Dark
    Light

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.

Features

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

Strengths

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

Weaknesses

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.

Dependancies

SSIS has a dependancy on a SQL Server license

Hosting

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.

Costs

To run SSIS in production you need to host/run the package on a machine which is covered by a SQL Server license.

Related Technologies

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.

Recommendation

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.

Learn More

Was This Article Helpful?