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. In the
SQL Server 2016 preview release, there is also a preview of the new SQL Server
Data Tools which will support backwards compatibility. This tip introduces the
new feature.

Solution

SQL Server 2016 Preview

At the time of writing, SQL Server 2016 is still in preview (currently
Release Candidate 1 has been released). This means functionality or features of Integration Services might change, disappear or be added in the final release.

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. In SQL Server 2016, the BI tools is now
coupled together with the database tool and the entire tools is just named
SQL Server Data Tools or SSDT. An overview:

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 release of SSDT solves these issues: by introducing backwards
compatibility you can use one single version of SSDT to develop and maintain SQL
Server 2012, 2014 and 2016 SSIS projects. It's important to note that SSAS and
SSRS support backwards compatibility for quite some time now.

SSIS and Backwards Compatibility

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. 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.

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, 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:

About the author

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.

I'm afraid the ability to use control flow parts in older projects is gone. With the GA release of SSDT, the "Package parts" entry in the solution explorer is only available when the target server version is SSIS 2016. This is a very odd decision, as it worked perfectly in the betas and (according to MS' own docs) it's a design-time feature so there seems to be no reason to disable it. Other than commercial reasons, that is...

Koen, thanks for the post. It was interesting to know smth new about SSDT 2015.

But is it really true that 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? I created a new project and package with SSDT 2013, deployed to SQL Server 2012 and ran it successfully.

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.