Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Integration Services SSIS Versions and Tools



By:
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
  • VS 2010. Templates renamed to SQL Server Data Tools (SSDT). This tool came with the SQL Server installation media.
  • VS 2012. SSDT. Separate download. Because of confusion with the database tools in Visual Studio (also called SSDT), the templates were renamed to SQL Server Data Tools for Business Intelligence (SSDT-BI).
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.

Last Update: 8/14/2017




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools