1

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?

kyarbles
  • 169
  • 1
  • 9
  • 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 May 07 '18 at 13:22
  • 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 May 07 '18 at 13:39
  • Which version of SSDT/Visual Studio are you using? – billinkc May 07 '18 at 18:01
  • 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 May 07 '18 at 20:09

1 Answers1

1

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.

Ferdipux
  • 5,116
  • 1
  • 19
  • 33
  • I finished updating both VS 2015 and 2017 with the latest versions of everything including VS itself and SSDT. Rebuilt the solution in both and tried once from each rebuild. Still have the same problem and error when deploying from the ispac file. Is there something I can check on the server side? I have sysadm there. – kyarbles May 08 '18 at 19:23
  • Hmm. Your problem case - are you deploying ispac with SSMS? What version of SSMS are you using? – Ferdipux May 10 '18 at 05:06
  • Just clicking on the ispac file itself. The idea is to be able to hand this file off to our deployment team because they would rather not do this through Visual Studio. Build/deploy from visual studio runs through almost the same options as the ispac file but just happens to work 100% as expected. SSMS version is v17.3 – kyarbles May 14 '18 at 14:49
  • Updated the reply to address your goal with IsDeploymentWizard. – Ferdipux May 15 '18 at 04:32