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

Solution 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

Solution 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.

Comments

[billinkc]

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?

[Gilbert Gamache]

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

[billinkc]

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

[Gilbert Gamache]

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]

But my problem is that I am deploying my package on a SQL 2012 server but using an SQL 2014 API. So the package won't run once deployed. Thanks for the heads-up