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

Answer 1

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

Answer 2

I was able to get this resolved. The issue we had was that the server had both SQL Server 2012 and SQL Server 2014 installed. I was trying to install a package to the SQL 2012 instance, but even though you use VS 2010 Shell to develop the SSIS package, when you run the .ispac file in the file system it was kicking off the ISDeploymentWizard.exe from C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\ location. This was causing the package to automatically get upconverted to version 8 and so each time you executed the package on the SQL Server you would receive the " Error loading value "8" from node "DTS:Property" error.

To resolve simply do the following: If you're deploying the package to SQL Server 2012 run the executable at this location: C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\ISDeploymentWizard.exe

If you're deploying the package to SQL Server 2014 run the executable at this location: C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\ISDeploymentWizard.exe

Then follow the wizard as normal to install the package.