Problem
SQL Server Data Tools (SSDT) is the development environment for creating and maintaining Integration Services (SSIS) packages and projects. Historically, there was no backwards compatibility, meaning that with a newer version of SSDT, you couldn’t create SSIS packages for an older version of SSDT. Since the SQL Server 2016 release, SQL Server Data Tools supports backwards compatibility up to SQL Server 2012. This tip explains the new feature.
Solution
Introduction
Historically, every release of SQL Server had an accompanying release of a business intelligence development environment. This was always either a set of templates installed into Visual Studio, or if Visual Studio was not already present, a shell of Visual Studio only capable of handling BI projects. With this tool, you could develop Integration Services, Analysis Services and Reporting Services projects. Initially, this tool was called Business Intelligence Development Studio or BIDS. In SQL Server 2012, it was renamed to SQL Server Data Tools. However, Microsoft had also another product called SQL Server Data Tools; with this tool you could create and manage SQL Server database projects. The BI tool was available on the SQL Server installation media, while the database tool was a separate (and free) download. Because this caused quite some confusion, SSDT was renamed to SQL Server Data Tools for Business Intelligence or SSDT-BI. Since SQL Server 2016, the BI tools are now coupled together with the database tool and the entire toolset is now named SQL Server Data Tools or SSDT. An overview:
- SQL Server 2005 – Visual Studio 2005 (BIDS)
- SQL Server 2008 and 2008R2 – Visual Studio 2008 (BIDS)
- SQL Server 2012 – Visual Studio 2010 (SSDT, available on SQL Server installation media) or Visual Studio 2012 (SSDT-BI, available as a separate download)
- SQL Server 2014 – Visual Studio 2013 (SSDT-BI, available as a separate download)
- SQL Server 2016 – Visual Studio 2015 (SSDT, available as a separate download and incorporates database projects as well)
- SQL Server 2017 – Visual Studio 2015 or Visual Studio 2017 (SSDT)
The problem here was that SSIS didn’t have any support for backwards compatibility. For example, if you wanted to develop packages for SQL Server 2008, you needed Visual Studio 2008. If you wanted to develop for SQL Server 2014, you needed Visual Studio 2013. With Visual Studio 2013, you couldn’t develop SSIS projects for SQL Server 2008 or any other version of SQL Server except for SQL Server 2014. This meant that if you worked with several versions of SSIS, you ended up with lots of different versions of Visual Studio on your development machine.
The latest releases of SSDT (since SQL Server 2016) solve these issues: by introducing backwards compatibility you can use one single version of SSDT to develop and maintain versions of SSIS projects from 2012 up to 2017 and later. It’s important to note that SSAS and SSRS support backwards compatibility for quite some time now.
SSIS and Backwards Compatibility
The remainder of this tip was written using the Visual Studio 2015 SSDT release, together with SQL Server 2016. All of the advice given in this tip is valid for later versions as well.
First of all we need to install the latest version of the Visual Studio 2015 SSDT preview release. When you open up the SQL Server 2016 installation media, you can find a link to the download page. It also contains a link to the download page of SQL Server Management Studio (SSMS), since this is now also a separate download.
Creating a new project
When you add a new project, you can see it’s now possible to create database projects and BI projects as well in SSDT.
In the project properties, you can set the target SSIS version that SSDT will use for this project.
The default target version is SQL Server 2016 (in later versions of SSIS that will be the most recent version of SQL Server). SQL Server 2014 and SQL Server 2012 are supported as well, while older versions (2005 and 2008) are not. If you have older projects than SQL Server 2012, you will need to upgrade them first before you can use the latest SSDT version.
Once the target version is set, the SSIS toolbox will adapt accordingly. For example, in SQL Server 2016 there are new Hadoop tasks available and you can download Azure tasks from the Azure feature pack.
When setting the version to SQL Server 2014, all those new tasks will disappear from the SSIS Toolbox. The same is true for data flow transformations introduced in SQL Server 2016.
When changing the version of a project, you will get a warning that existing packages might be changed:
If you use SQL Server 2016 functionality though, you will get an error after setting the target version to an earlier version when opening the package:
The package will still open, but offending tasks, transformations or connection managers might disappear, or you are unable to open up an editor. It’s possible that switching back to SQL Server 2016 doesn’t solve the problem: the object might still be broken. In that case there is no other option than to remove the object and add it again.
At the time of writing, a couple of bugs exist when switching between target versions. You can find a list at the MSDN blog post What’s New for SSIS 2016 RC0?. It’s possible some of those are already fixed when you read this.
When you have your own custom components in SSIS, you need to take some extra steps to make them work with the target version property. SQL Server MVP Joost van Rossum explains how you can do this in his blog post Switching Target Server Versions for custom components.
Adding existing packages to the project
When you add an existing SSIS package from an earlier version to the project – for example a SSIS 2012 package to a 2016 project – the package will be upgraded to match the target version.
When the versions match, no upgrade takes place of course, the package is directly added to the project. You can also add packages from a higher version to the project, SSIS will try to downgrade them.
Testing shows however you can’t always trust this message. Even with a success message, the package can be broken if you use SQL Server 2016 only components.
Opening an existing project
When you use SSDT 2015 to open a project created with an earlier version of SSDT, the upgrade wizard will automatically kick-in. This means all of the packages are upgraded to SQL Server 2016.
Although you can still downgrade the project back to the earlier version, going through the upgrade process is maybe something you do not want. As an alternative, you can edit the project file and add the following line:
This will make SSDT 2015 skip the upgrade wizard and directly open the project with the correct target version. For SQL Server 2014, you change SQLServer2012 into SQLServer2014 of course. Although this work around seems to work, manually editing the XML of the project file is not really supported. My advice is to create a new empty project, set the target version and then start adding packages.
Control Flow Parts
Control flow parts are introduced in the tip SQL Server Integration Services 2016 Control Flow Templates Introduction (they have been renamed from templates to parts). Surprisingly, you can still work with control flow parts if your target version is not SQL Server 2016. This is possible because they are a design-time feature and they don’t influence how packages actually work. In the example here I have created a new project with the target version set to SQL Server 2014. I created a simple control flow part and added it to the package.
Debugging the package is not a problem.
And you can also run the package inside a SQL Server 2014 SSIS Catalog:
Conclusion
The Target Server Version property introduces backwards compatibility for SSIS project in SSDT 2015. With this new capability, an old sore of SSIS has finally been fixed: you can use only single version of Visual Studio to manage your different SSIS projects. It is recommended to set the server version at the start of the project and that you don’t switch it around too often as it may lead to issues. The good news is that with SSDT 2015 and later, you can also use control flow parts in your older projects!
Next Steps
- For more information on the different versions of SQL Server Data Tools:
- For more information on Control Flow parts, check out the tip SQL Server Integration Services 2016 Control Flow Templates Introduction.
- For more SQL Server 2016 tips, you can use this overview.
