SQL Server Integration Services SSIS Versions and Tools

Overview

In this section, we’ll briefly discuss the history of the Integration Services product and the tools you would use to create SSIS projects.

Explanation

Before SSIS, SQL Server came with Data Transformation Services (DTS), which was part of SQL Server 7 and 2000. For SQL Server 2005, the teams at Microsoft decided to revamp DTS. Ultimately, they ended with a replacement for DTS instead of just an upgrade and because it was such a drastic change, it was decided to name the product Integration Services instead of DTS. This name change came late in the product development cycle and that’s why some objects still refer to DTS. For example, the command line tools DTEXEC (to execute SSIS packages) and DTUTIL (to deploy packages to a server). Integration Services was launched with SQL Server 2005 and the most basic core functionality is still the same today. It was a drastic change with DTS, and it quickly became a popular ETL due to it’s speed, flexibility and its support for various sources.

With SQL Server 2008, lots of performance improvements were made to SSIS and new sources were introduced as well. SQL Server 2008R2 didn’t introduce any noticeable changes for SSIS.

SQL Server 2012 was a major release for SSIS. It introduced the concept of the project deployment model, where entire projects with their packages are deployed to a server, instead of individual packages. The SSIS of SQL Server 2005 and 2008 is now referred to as the (legacy) package deployment model. SSIS 2012 made it easier to configure packages and it came with a centralized storage and management utility: the catalog. We’ll dive deeper into those topics later on in the tutorial.

SQL Server 2014 didn’t have any changes for SSIS, but on the side new sources or transformations were added to the product. This was done by separate downloads trough CodePlex (an open-source code website) or through the SQL Server Feature Pack. Examples are the Azure feature pack (to connect to cloud sources and objects) and the balanced data distributor (to divide your data stream into multiple pipelines). In SQL Server 2016 there were some updates to the SSIS product. Instead of deploying entire projects, you can now deploy packages individually again. There are additional sources – especially cloud and big data sources – and some important changes were made to the catalog. You can find an overview of all those new features here and here.

SQL Server 2017 brought a completely different set of updates: it allows SSIS (and the database engine itself) to run on Linux. It also introduced a “scale out” feature, where you can distribute the execution of SSIS packages over multiple worker nodes, managed from a single master computer. With SQL Server 2019, some changes were made to make working with files in the cloud easier: the “flexible file task” and the “flexible source/destination”. These new components can for example work with Avro or Parquet files, but they require the presence of a Java runtime environment. SQL Server 2022 introduced nothing new for SSIS.

Alongside the SQL Server releases, Azure Data Factory launched an “Azure-SSIS” integration runtime, which allows you to run SSIS projects inside Azure Data Factory.

During all these years, SSIS has built itself a reputation for being a stable, robust, and fast ETL tool with support for many sources. However, it’s still mainly an on-premises solution, there is, at the time of writing, no real cloud alternative.

Tools to develop Integration Services Projects

Integration Services projects are always developed using Visual Studio. However, you don’t need the have the full-blown Visual Studio installed on your machine. It’s also possible to install just the business intelligence templates, which will install a shell of Visual Studio instead. Traditionally there was no backwards compatibility in SSIS projects, which means for every version of SQL Server, there was a specific version of Visual Studio you had to use. Unfortunately, the tools also changed names a couple of times, which makes it a bit harder to search for it on the Internet. An overview:

Since SQL Server 2016, it’s possible to develop projects for earlier versions of SSIS within the same version of Visual Studio. In the latest version, you can develop projects for SQL Server 2017, 2016, 2014 and 2012. The tip Backwards Compatibility in SQL Server Data Tools for Integration Services explains the concept in more detail. This means that If you want to follow along in this SSIS tutorial, you can use the latest version of Visual Studio and the relevant SSIS extension, and then switch to the correct compatibility level.

Additional Information

• You can find all SSIS tips here.