By: Koen Verbeeck
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.
History
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 become a popular ETL due to its 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 new 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 new features here and here.
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:
| SSIS Version | Visual Studio Version |
|---|---|
| SSIS 2005 | VS 2005 – templates were called Business Intelligence Development Studio (BIDS) |
| SSIS 2008 / 2008R2 | VS 2008 (BIDS) |
| SSIS 2012 |
|
| SSIS 2014 | VS 2013. SSDT-BI. Separate download. |
| SSIS 2016 | VS 2015. Database tools and business intelligence tools are combined into one single product: SSDT. Separate download. |
If you want to follow along in this SSIS tutorial for SQL Server 2016, you can download the latest version of SSDT here. Make sure you download the templates for Visual Studio 2015. At the time of writing, SQL Server 2017 hasn’t been released yet, but with the latest version of SSDT for Visual Studio 2015 (SSDT 17.2) you can already develop projects for SQL Server 2017.
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.
Additional Information
- You can find all SSIS tips here.
