Introduction to SSIS

SQL Server Integration Services (SSIS) is a popular data integration software and also a workflow application very useful to create ETLs and designed to be part of the Business Intelligence tools of Microsoft. In this article, we will discuss the evolution of this software and describe where it is going.

Data Transformation Services in SQL Server 7

Everything started on SQL Server 7 which was released in 1998. This was a simple tool to export and import data and to automate some tasks. The name was Data Transformation Services.

Data Transformation Services in SQL Server 2000

SQL Server 2000 became a very popular database. Microsoft was starting to compete with Oracle and sold some millions of SQL Server licenses around the world. At that time, the Data Transformation Services were handled in the SQL Server Enterprise Manager, which was replaced with SSMS later. Data Transformation Services had some connections and a few tasks.

In SQLServerCentral we have an old article about DTS to make you feel nostalgic: Overview of DTS Packages

SSIS 2005 BIDS

SQL Server 2005 changed completely the interface to create packages. The name was changed from Data Transformation Services to SQL Server Integration Services. The tool to create packages is not so different from what we have now:

In SSIS 2005, we use to work with the BIDS (Business Intelligence Development Studio) which was the father of the SSDT. BIDS was included in the SQL Server Installer. The tasks, sources, destinations growth dramatically. At that time, it was a serious and professional tool to integrate data.

At SQLServerCentral, we have an article to migrate Data Transformation Services: Upgrading SQL Server 2000 DTS Packages to SSIS

If you want to see an introduction of SQL Server 2005, here you have one of our articles: Overview of SSIS

SSIS 2008

In SSIS 2008, they added an ADO.net source, Data Profiler, an enhancement to the lookup transformation. Allow using the Change Data Capture Feature and more features.

Here we wrote an article about compatibility errors between BIDS 2005 and 2008.

Data Profiling Task

SSIS 2012 - SSDT

Starting on SQL Server 2012, the BIDS was replaced with the SQL Server Data Tools (SSDT). SSDT is an extension of Visual Studio used to create SSIS projects and other types of projects. With that version, there were SSIS Deployments, Zoom, Undo,  enhancements. The SSIS toolbox, Change Data Capture components, ODBC Source, and Destination components, an Expression Task, Data Quality Services task, parameters, environments, and other features.

Currently, the installer is still available here, but it may change in the long run: SSDT 2012 installer.

SQL Server Integration Services 2014 - SSDT

In SQL Server 2014, the SSDT was removed from the installer. Starting on that version, it was necessary to install separately. The SSDT started to be part of VS, so the version of SSDT matches with the Visual Studio version.

SSDT depends on Visual Studio. Currently, the VS versions available with SSDT are the following:

1. Firstly, SSDT for VS 2012
2. Secondly, SSDT for VS 2013
3. Thirdly, SSDT for VS 2015
4. Also, SSDT for VS 2017
5. In addition, SSDT for VS 2019
6. Finally SSDT for VS 2022 Preview

In VS 2019 and VS 2022 in the Data Storage and processing, you will find the SQL Server Data Tools:

Firstly, to create SSIS projects, you will need to go to Visual Studio and go to Extensions>Manage Extensions.

You will need to search for SSIS and download SQL Server Integration Services Projects, and press download.

Once downloaded, you need to install the executable file.

SQL Server Integration Services 2016

SSIS 2016 came with the following changes:

• Incremental Package Deployment
• Support for Always on for the SSIS catalogs.
• Support for Always Encrypted SSIS catalogs.
• Enhancements on debugging and logging levels.
• Support for OData, Big Data, SAP BW, Teradata, PDW
• Azure Storage connectors and also Hive and Pig
• Support for  Microsoft Dynamics online, Azure Data Lake, and Synapse.

SSIS 2016

SQL Server Integration Services 2017

One of the new changes was the support of Linux. You can run SSIS packages on Linux computers. Also, the Scale-out allows executing packages across several computers. Starting on this version you can deploy packages in Azure Data Factory.

SQL Server Integration Services 2019

On this version, they added a Flexible file task that allows to work with files locally and in Azure. Microsoft also added the Flexible source file and destination with the same concept.

What is the future of Integration Services?

SQL Server Integration Services is still a very popular software for Data Integration. Informatica, SSIS, Pentaho, Qlik, Talend, ODI, Azure Data Factory are some of the most popular Data Integration,  applications in the World.

However, Microsoft retired from the SSIS certification exams in January 2021. Also, there were not too many enhancements in the last versions. The plan in my opinion is that Microsoft will not continue improving SSIS and will work on Azure Data Factory to replace SSIS in the long run. In general, Microsoft is trying to convince anyone to move to Azure. The companies by the moment are usually in a mixed-mode (some on-premises servers and some services in the Cloud including Azure, AWS, Google, and other cloud platforms). So, my guess is that SSIS will lose popularity in the long run and Microsoft will deprecate it in 10-20 years.