Backwards Compatibility in SQL Server Data Tools for Integration Services

By:   |   Comments (13)   |   Related: 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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Friday, July 31, 2020 - 10:02:31 AM - Koen Verbeeck Back To Top (86225)

Hi Nagraj,

what is the exact error that you get?
Which version of SSDT are you using?
Do you get the error for every script task, or just one?

Koen

Thursday, July 30, 2020 - 8:31:12 PM - Nagraj Back To Top (86220)

Hey good one. I have a situation where I need to upgrade all my packages of our project need to be migrated from SQL server 2008r2 to SSDT 2015. I have done most f migration with lot of huddles but only one error popsup which says integration service is not compatible as script task uses version 15,I use SQL server 2016 .I donno how to resolve this issue.plz suggest on this. I cannot go back and chage SsDT version

Monday, November 11, 2019 - 7:12:32 PM - Gany Back To Top (83062)

Oh, wow. That's nuts!  Thanks for this very detailed explanation!

Monday, February 4, 2019 - 8:06:48 AM - Koen Verbeeck Back To Top (78961)

Hi Vims,

if you use SQL Server Data Tools (which you can download here: https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017), it is free. However, there are two options during installation:

* either you install it as a stand-alone version (which is the only option you have if you don't have Visual Studio installed). In this case, a shell of Visual Studio is installed and it is totally free.

* either you install the SSDT templates in an existing installation of Visual Studio. In that case, you need to have a license for that edition of Visual Studio.

Monday, February 4, 2019 - 6:16:43 AM - vims Back To Top (78959)

Hi SQL Server Data Tools 2015 or 2017 free software or liscense required?

Thursday, January 3, 2019 - 8:39:27 AM - Koen Verbeeck Back To Top (78614)

I'm using SSDT 2017 as well and I can choose SQL Server 2012 without a problem.
Are you using any task/components which are not available in 2012?

The error message you provided typically occurs when SSIS cannot open the XML. This can be because it is corrupt, or because the versions don't match.

Sunday, December 23, 2018 - 6:39:22 PM - Dan Back To Top (78545)

I'm using SSDT 2017 and the oldest target SQL version it allows is 2014. I'm trying to deploy to Integration Services for SQL Server 2012 SP3 (11.0.6260) and it's failing with error:

The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.

Your thoughts?

Tuesday, May 29, 2018 - 8:17:23 PM - Patrick Lendrum Back To Top (76047)

 ​I have found a SOLUTION!!!  This is for 2012 solution/project but should work for all...

1) Install SSDT for VS 2017; I ONLY installed Integration services (NOT Analysis or Reporting Services)

2) Restart Computer after install

3) Open Visual Studio-->Tools-->Extensions and Updates-->ENABLE Integration services extension (NEVER knew this was an extension/option)

4) Restart Visual Studio

5) Open solution.  The Project may still error; if it does, right click on the project and rebuild project.  It should now open and have the version in parenthesis

 

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

@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 5, 2016 - 10:47:50 AM - Arthur Back To Top (41819)

 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 (41734)

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 2, 2016 - 3:30:08 PM - Koen Verbeeck Back To Top (41388)

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 2, 2016 - 10:37:52 AM - Aleksandr Back To Top (41386)

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.














get free sql tips
agree to terms