Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Backwards Compatibility in SQL Server Data Tools for Integration Services


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

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


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:

  • 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)

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.

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. 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, you can also use control flow parts in your older projects!

Next Steps


Last Update:


signup 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





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    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, August 24, 2016 - 7:28:22 AM - Koen Verbeeck Back To Top

@Arthur: I assume you're refering to the SSIS instance? That would only be needed if you're still working with the package deployment model. (If possible, the project deployment model is preferred.)


Tuesday, July 05, 2016 - 10:47:50 AM - Arthur Back To Top

 Apparently, unlike SSDT-BI the SSMS 2016 is NOT backward compatible with the earlier releases of SSIS. Source: https://msdn.microsoft.com/en-us/library/mt238486.aspx?f=255&MSPPError=-2147217396

 


Wednesday, June 22, 2016 - 2:39:58 AM - Jeroen Vaes Back To Top

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


Monday, May 02, 2016 - 3:30:08 PM - Koen Verbeeck Back To Top

Hi Aleksandr,

normally there's an error about the version number when you run SSIS 2014 packages on a 2012 server.
There's a whole connect thread about people having backward compatibility issues:

https://connect.microsoft.com/SQLServer/feedbackdetail/view/944882/ssdt-bi-2014-backward-compatibility-for-ssis-2012

Maybe MS fixed things after a while :)


Monday, May 02, 2016 - 10:37:52 AM - Aleksandr Back To Top

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.


Learn more about SQL Server tools