ispac file deployment errors out when build/deploy from visual studio works fine

Question

We have two packages as part of one job. We copied these same packages from one SQL Server Integration Services Catalog SSISDB folder to another SQL Server in the same Integration Services Catalog SSISDB folder (same namespace). When running this job on the new server with the packages copied, the job runs but fails and also does not produce an error message (we have any error message logged).

When building and deploying from Visual Studio these same two packages to the same SSISDB folder and then running the job, the job runs as expected.

When deploying via the .ispac file, the script tasks we have error out with with

ScriptTaskName: There was an exception while loading ScriptTask from XML: 
System.Exception: The Script Task "ST_0001a..." uses version 15.0 script 
that is not supported in this release of Integration Services. To run the 
package, use the Script Task to create a new VSTA script. In most cases, 
scripts are converted automatically to use a supported version, when you 
open a SQL Server Integration Services package in %SQL_PRODUCT_SHORT_NAME% 
Integration Services. at 
MicrosoftSqlServer.Dts.Tasks.ScriptTaskScriptTaskLoadFromXML(XmlElement 
elemProj, IDTSInfoEvents events)

How come only the build and deploy way makes the packages work as intended?

In the project properties, do you have the Target Server Version set to the same version of SQL Server that you are deploying to? — Tab Alleman
It is set to "SQL Server 2016" and the target server version from the @@VERSION command is "Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) ..." — kyarbles
Most of the time it was tried from Visual Studio 2015 Version 14.0.24720.00 Update 1and SSDT 14.0.608.142. Tried today with Visual Studio 2017 version 15.6.6 and SSDT 15.1.61801.210 same results with both ispac files built from either setup. — kyarbles

Ferdipux Ferdipux · Accepted Answer · 2018-05-08T09:09:35

There are several possible causes of such error.
First - you should update your version of SSDT from MS to the latest, set up correct target SQL Server version in the project properties and rebuild the packages. It might be the case when this property is reset to SQL vNext.
There are statements that your version of SSDT contained errors leading to this error.

Second - by some reason when deploying .ispac from SSMS or by clicking it at File Explorer, SSMS implicitely upgrades the project and packages in it to its own version. Detailed report on this behavior. I suspect you have SSMS 2017 and it performs such implicit upgrade of Script tasks to version 15.
If you want to deploy package by Support Team without using SSMS, here is the way. Use IsDeploymentWizard - Microsoft utility to manage package and project deployment. It has both GUI and command-line parameters to handle the deployment.
Here is a sample from one of projects:

isdeploymentwizard /S /SP:"D:\Project\Proj.ispac" /DS:"myserver\instance" /DP:"SSISDB/ProjectFolder"

Ensure that IsDeploymentWizard is started from SQL bin directory %ProgramFiles%\Microsoft SQL Server\130\DTS\Binn, not from SSMS.

ispac file deployment errors out when build/deploy from visual studio works fine

1 Answers