2

I recently upgraded a SQL Server from 2008 R2 to 2016 and a SSIS package on the SQL server (in msdb) contained ActiveX Script Tasks. So I exported the package and got the code out of the ActiveX Script Tasks and updated it in regular Script Tasks, pretty simple package. I imported the SSIS package back onto SQL Server (in msdb) and thought all was well. As the job showed it executed and completed successfully. I am later notified that nothing happened that the package should of done. I even executed the package manually in Integration Services and yet still nothing other than it running in about .15 seconds (which this job should take about 5 seconds) and showing it completed successfully. The package actually does its what it is supposed to do when ran from a VS 2015 (with SSDT) project, but does nothing when it is on SQL Server. Has anyone experienced this!?! I recall something about 32bit runtime vs 64bit runtime but I wanted to check here first. Any solutions? I've even started from scratch with a NEW project too.

enter image description here

  • It's likely that SSIS is simply reporting that the Script Task completed successfully. Instead consider coding something into the script task that looks to see if the data actually transferred successfully, then report status failure or success using Dts.TaskResult = (int)ScriptResults.Failure or Success as needed. – Shooter McGavin Dec 21 '18 at 17:59
  • I think the issue is, even if the script task throws an error during execution, it still completes. I would wrap any commands in the script with a try{}catch(){}. If it ends up in the catch, pass the Dts.TaskResult = (int)ScriptResults.Failure. That will at least ensure you will get the correct failed status. Also you should be able to catch and report or log the exception(assuming there is one). – Shooter McGavin Dec 21 '18 at 20:23
  • Also, since you said it seems the script task is what is actually failing, it would probably be a good idea to paste the contents of the script task in your question. That might shed some more light on the answer. – Shooter McGavin Dec 21 '18 at 20:24
  • Wrap the copy operation in the script task in a try catch block. Then place the failure status in the catch block. If the copy operation fails the try catch should catch it. – Shooter McGavin Dec 25 '18 at 19:40
  • I was completely unaware that Deployment Target Version defaults to SQL Server 2017 in Visual Studio 2015 SSDT ... It ran fine once I had set it to SQL 2016... I feel like a fool. – Eric Swiggum Jan 14 at 23:44
1

I was completely unaware that Deployment Target Version defaults to SQL Server 2017 in Visual Studio 2015 SSDT ... It ran fine once I had set it to SQL 2016... I feel like a fool.

[https://feedback.azure.com/forums/908035-sql-server/suggestions/32896399-script-tasks-losing-code-in-ssdt-17-1-for-visual-s][1]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.