SSIS package upgraded by deployment

Question

In our QA virtual environment, which contains multiple SQL server, i wanted to deploy an SSIS 2012 package (ispac, project deployment) maintained through Visual Studio 2010. The destination SSIS server was 2012 but the client on the workstation included SQL server 2014. By executing the ispac package on the workstation and specifying to deploy on the SQL server 2012, the deployment went without any errors. But when executing the package on the SSIS server we get errors such as

"Package Name" : Error: The version number in the package is not valid. The version number cannot be greater than current version
number.

"Package Name" : Error: Error loading value "8" from node "DTS:Property".

"Package Name" : Error: 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.".

All my package (.dtsx) have

 <DTS:Property DTS:Name="PackageFormatVersion">6</DTS:Property>

As well as the manifest

<SSIS:Property SSIS:Name="PackageFormatVersion">6</SSIS:Property>

It looks like the SQL 2014 client or the workstation upgraded my package to V8 even though my destination server was V6. When I deployed directly from the SQL 2012 server (which didn't have SQL 2014) everything deployed and ran as expected. Is This the expected result ? or a problem

You developed the solution using SQL Server Data Tools 2012. The machine also contains SSDT 2014. When deployed to a 2012 server, it is reporting the error that you have deployed a 2014 solution, yes? — billinkc
This has nothing to with the machine were the solution was developed, all my package are version 6 (which is SSIS 2012). I think the problem comes from the workstation from which the solution was deployed that contains SSIS 2014 but onto an SSIS 2012. The development machine contains SQL2012, VS 2010, VS 2013 — Gilbert Gamache
Right, the tea has not caught up with my brain yet ;) So what you're seeing is the seamless updating of packages. There are two different deployment wizards, ISDeploymentWizard.exe in C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\ISDeploymentWizard.exe and C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\ISDeploymentWizard.exe and the Windows $PATH variable has the 120 in the search path first. If you redo the deploy, this time specifying the 110 version of the wizard handles the ispac, does it resolve the issue? — billinkc
See also How to deploy a existing SSIS Package in sql server 2012 — billinkc
Yes, it works. Is this an expected behaviour, that a package deployed using a 2014 deployer upgrades the package even if the deployment is done on a 2012 SSIS Server — Gilbert Gamache

billinkc billinkc · Accepted Answer · 2015-01-22T20:59:10

So what you're experiencing is expected behaviour ^{citation needed}. When an SSIS package is opened using the APIs, the package is updated to that APIs version. This allows a V-1 package to run on a Vcurrent server. There were changes to the formats and stuff so there are reasons a 2005 package may not run on a 2014 box but that's the intent. The bits on disk remain unaltered but the in-memory version is what gets updated.

Since the deployment used the ISDeploymentWizard in the 120 folder (SQL Server 2014), the first thing it did when it saw the 2012 version of the .ispac is convert it to a 2014 format. So, the in-memory version of this .ispac needs to get serialized into the SSISDB and those APIs are the same between 2012/2014. The DeployProject/deploy_project method just takes a binary object, it doesn't do any validation of what version those bits are, just that it has the right shape.

But, when you go to execute the package, that's when the API needs to look at the actual bits in there and discovers, this is a version I don't understand.

Some examples of what that API looks like on How to deploy an existing package in SQL Server 2012

SSIS package upgraded by deployment

2 Answers