How to Build SSIS Package for Different SQL Server Version with Visual Studio 2017 and SSDT

After deploying an SSIS package built with Visual Studio 2017, we encountered a surprise when running it via a Command Prompt:

Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.
 
C:\Windows\system32>"D:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe" /File "D:\PROG\bin\SSIS_Extract.dtsx" /Conf "D:\PROG\bin\SSIS_Extract.dtsConfig"
Microsoft (R) SQL Server Execute Package Utility
Version 11.0.2100.60 for 64-bit
Copyright (C) Microsoft Corporation. All rights reserved.
 
Started:  5:04:56 PM
Error: 2018-11-26 17:04:56.20
   Code: 0xC001700A
   Source: SSIS_Extract
   Description: The version number in the package is not valid. The version number cannot be greater than current version number.
End Error
Error: 2018-11-26 17:04:56.20
   Code: 0xC0016020
   Source: SSIS_Extract
   Description: Package migration from version 8 to version 6 failed with error
0xC001700A "The version number in the package is not valid. The version number cannot be greater than current version number.".
End Error
Error: 2018-11-26 17:04:56.21
   Code: 0xC0010018
   Source: SSIS_Extract
   Description: Error loading value "<DTS:Property xmlns:DTS="www.microsoft.com/
SqlServer/Dts" DTS:Name="PackageFormatVersion">8</DTS:Property>" from node "DTS:
Property".
End Error
Could not load package "D:\PROG\bin\SSIS_Extract.dtsx" because of error 0xC0010014.
Description: 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.
Source: SSIS_Extract
Started:  5:04:56 PM
Finished: 5:04:56 PM
Elapsed:  0.203 seconds
 
C:\Windows\system32>

Pay attention to this section of error message:

Description: Package migration from version 8 to version 6 failed with error
0xC001700A "The version number in the package is not valid. The version number cannot be greater than current version number.".

What’s the real problem? It turns out that the SQL Server version in the production server is older than the version of the tool. The package was built with SSIS designer for Visual Studio 2017 (version 8), however the target database is SQL Server 2012 (version 6). So what do we need to do?

In the past, we needed to install an older version of Business Intelligence Development Studio that matches the SQL Server version we’re targeting. Because of this, many developers still have the habit of having several versions of Visual Studio installed on their machines just to handle every version of SSIS package. Fortunately with the newer of Visual Studio with SSDT, we can target the latest SQL Server (2017) down to 2012.

How do we target an older version of SQL Server?

Before we begin with the steps, make sure your Visual Studio 2017 has the SQL Server Data Tools installed. Otherwise, check the SSDT download at the bottom of this post.

How to target a specific version of SQL Server in SSIS

1. In your project (e.g., Temp1), right-click on the project name and click Properties (or press Alt-Enter).

   

2. Once the Property Page is opened, click on the Configuration Properties and check the TargetServerVersion.

   

3. Click on the drop-down list and select the correct SQL Server version on the machine where you want to deploy your SSIS package and click OK.

4. Rebuild your SSIS package and re-deploy the package.

This time you will be able to run the package without any problems.

Update 2 May 2019: Since the posting of this article, apparently Microsoft removed support for SQL Server 2012 in SSDT version 15.8.1, but added it back in version 15.9.0. Check your SSDT version if you don’t see an option to target SQL Server 2012.

Make sure you pay attention to the notes marked as Important. For example, before installing SSDT for Visual Studio 2017 (15.9.1), you need to uninstall Analysis Services Projects and Reporting Services Projects from Tools, Extensions and Updates and close all Visual Studio instances.

Download

SSDT for VS 2017 Installer

Further Reading

How to Create SSIS Package in Visual Studio 2017
SQL Server Data Tools
Download and install SQL Server Data Tools (SSDT) for Visual Studio
How to Add Custom Logging to SSIS Package