A Tale of Two Properties: SSIS ProjectVersion and TargetServerVersion

I’m typing this at 4:30 AM IST in my hotel room in Bengaluru, India. It’s not unusual that I am awake at 4:30 AM. It is unusual that I’ve been awake since around 1:15 AM. I am attending and presenting at the Data Platform Summit 2018 and, obviously, still adjusting to the time zone. It is an honor to be here! Amit, Manohar, and a small army of team members work around the clock – literally – to bring this awesome conference together. The passion and energy here is inspiring! If you have an opportunity to attend DPS, I encourage you to do so.

From the Interwebz…

Since I am still adjusting to the 9.5 hour time zone shift, I decided to poke around social media. My friend and brother from another mother, TJay Belt, had tagged me in a tweet. TJay was looking for answers about updating an SSIS 2014 project in SQL Server Data Tools (SSDT) for Visual Studio 2017. TJay and I started this conversation a couple days ago when he mentioned a team member installed Visual Studio 2017 and experienced difficulties getting SSIS 2012 packages to execute in the debugger. I don’t think I blogged about it, but I had some interesting experiences upgrading to Visual Studio 2017 and SQL Server Data Tools for VS 2017. The sum of my experience was: uninstall everything, then install Visual Studio, then install SSDT. I shared this information. Today TJay asked a followup question:

I replied on Twitter – a lot – and realized in so doing that this deserved a blog post. You are welcome.

A Tale of Two Properties

TargetServerVersion

Since the release of SSIS 2016, SSIS developers have been able to select the version of SSIS via the TargetServerVersion property of the SSIS project. When new SSIS projects are created, the TargetServerVersion property defaults to the most up-to-date version available. The flexibility offered in this setting adds complexity, of course. But it’s neat to be able to use features of Visual Studio 2017 to build and maintain SSIS projects for earlier versions without having to maintain earlier versions of Visual Studio and SSDT.

ProjectVersion

SSIS projects maintain a ProjectVersion property in the dtproj file that informs Visual Studio (and SSDT) of the version of the XML format for the SSIS project. If you open an SSIS project built in SQL Server 2008 R2, for example, the ProjectVersion is 10.50.1600.1 or thereabouts:

For SSDT in VS 2017, the ProjectVersion is 14.0.3002.106 at the time of this writing:

What This Means

ProjectVersion does not affect TargetServerVersion.

Visual Studio checks the ProjectVersion property and, if needed, updates the SSIS project to the latest version. The ProjectVersion will be updated to match the current Product Version of the SSIS Designer template for SQL Server Data Tools which may be viewed from Help–>About:

Updated ProjectVersion, Same TargetServerVersion

Once an SSIS project has been updated to the latest ProjectVersion, the TargetServerVersion property may be used to target a previous version of SSIS. SSIS projects targeted at any version other than the latest version available are flagged in Visual Studio Solution Explorer with the version displayed in parentheses after the project name:

In this screenshot, the SSIS project is targeted for SQL Server 2014 (SSIS 2014) but it is loaded into SSDT for Visual Studio 2017. This feature means we can edit and maintain an SSIS 2014 project using Visual Studio 2017.

Some Possibilities

The functionality presents some interesting flexibility for SSIS developers. First, managing the TargetServerVersion property I can maintain the current Production version of an SSIS project using the latest tools, as long as the current Production version is SSIS 2012+. Second, I can – fairly easily – update the TargetServerVersion property to the latest version available by simply changing the value in a dropdown.