SSIS script task error on sql job

Question

i want to run a ssis package from sql job sql version is 2016 ssis package first was 2013 and have error with message as below

Started: 6:19:50 AM Error: 2018-02-10 06:19:50.57 Code: 0x00000003 Source: Script Task Script Task Description: There was an exception while loading Script Task from XML: System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.VisualStudio.Tools.Applications Version=14.0.0.0 Culture=neutral PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified. File name: 'Microsoft.VisualStudio.Tools.Applications Version=14.0.0.0 Culture=neutral PublicKeyToken=b03f5f7f11d50a3a' at Microsoft.SqlServer.IntegrationServices.VSTA.VstaHelper.b__1b() at Microsoft.SqlServer.IntegrationServices.VSTA.MtaExecution.Run(Action action) at Microsoft.SqlServer.IntegrationServices.VSTA.VstaHelper.CleanUp()
at Microsoft.SqlServer.VSTAHosting.VSTAScriptingEngine.DisposeVstaHelper() at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask.MigrateVSTADenaliScriptProject(XmlElement elemProj IDTSInfoEvents events) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask.LoadFromXML(XmlElement elemProj IDTSInfoEvents events) WRN: Assembly binding logging is turned OFF. To enable assembly bind failure logging set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1. Note: There is some performance penalty associated with assembly bind failure logging. To turn this feature off remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog]. End Error Error: 2018-02-10 06:19:50.58 Code: 0x00000003 Source: Script Task Description: The Script Task is corrupted.

then i install new version (2015) and now i have this error

02/10/2018 08:41:20,SSIS,Error,1,WIN2016-SQL2016\SQL2016,SSIS,SSIS,,Executed as user: WIN2016-SQL2016\Administrator. Microsoft (R) SQL Server Execute Package Utility Version 13.0.1601.5 for 32-bit Copyright (C) 2016 Microsoft. All rights reserved. Started: 8:41:20 AM Error: 2018-02-10 08:41:20.59 Code: 0xC000F427 Source: Script Task
Description: To run a SSIS package outside of SQL Server Data Tools you must install Standard Edition of Integration Services or higher. End Error DTExec: The package execution returned DTSER_FAILURE (1).

Answer 1

You should test the package on the real server. Some things do not work right in the simulators.

Answer 2

Anush, Based on your last error, try running the SSIS package as 32 bit instead of 64 bit.

Answer 3

I had the same issue and found it related to script tasks that were created in an earlier version of SSIS BI project that are not automatically converted.

Here is what I did to upgrade them. It might be a bit different for you but should give you some idea where to start.

Step 1. BACKUP. Make a backup of your SSIS projects and dtsx files.

Step 2. Create a new SSIS solution. Make sure that it indicates that in the VS Solution Explorer toolbox (see image 1).

Step 3. Check for the latest version of Microsoft.SqlServer.ScriptTask.dll you have under C:\Program Files (x86)\Microsoft SQL Server[Version NO]\DTS\Tasks. Make a note of that number.

Step 4. Open the dtsx file in a text editor (Notepad, Notepad++, etc.)

Step 5. Search for VSTAMajorVersion and change to the version you noted in step 3. See image 2.

Step 6. Replace the Microsoft.SqlServer.ScriptTask.dll reference in the file to the one you found in step 3.

Step 7. Add the dtsx files to the newly created SSIS solution and try to edit the sciprt tasks.

Step 8. Save and update your SQL Server Agent jobs to execute the newly created dtsx files.