CLOUD SQL 

Backwards Compatibility in SQL Server Data Tools for Integration Services



By:   |   Read Comments (6)   |   Related Tips: More > Integration Services Development







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.

SQL Server Installation Center

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.

Creating a New Project in the SQL Server Data Tools

In the project properties, you can set the target SSIS version that SSDT will
use for this project.

Set the Target Server Version for SSIS in SSDT

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
.

SSIS Toolbox supporting Hadoop and Azure

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.

With a SQL Server 2014 Compatibility Version the Hadoop and Azure SSIS toolbox items are gone

When changing the version of a project, you will get a warning that existing
packages might be changed:

Warning Message when changing the compatibility of an SSIS Package

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:

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.

Upgrade an SSIS package 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.

SSIS downgrade package message

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.

SSIS Package Upgrade Wizard

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:

Edit XML to avoid upgrading an SSIS Package

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.

SQL Server 2016 Control Flow Parts Available for SQL Server 2014 SSIS Packages

Debugging the package is not a problem.

Control flow parts debug

And you can also run the package inside a SQL Server 2014 SSIS Catalog:

Control flow parts execution from the 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




Last Update:

First Published: 2016-04-19

next webcast button

next tip button

About the author

MSSQLTips author Koen Verbeeck

Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips


Related posts

Leave a Comment