visual studio - SSIS script task errors out when executing from SQL Server Agent Job . System.IO.FileNotFoundException: Could not load file or assembly

Question

I have SQL Server 2019 installed on a Windows 2019 Virtual machine. Microsoft Visual studio Professional 2012 is installed on that server.

• I have bunch of SSIS packages in a project, some of those have script task written in C# 2012 and all those run fine from Visual Studio.

• Any package which has a script task fails when ran from SQL Server Agent job with following errors.

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=15.0.0.0, Culture=neutral, PublicKeyToken=xxxxxxxxx' or one of its dependencies. The system cannot find the file specified. File name: 'Microsoft.VisualStudio.Tools.Applications, Version=15.0.0.0, Culture=neutral, PublicKeyToken=xxxxxxxxxx' at Microsoft.SqlServer.IntegrationServices.VSTA.VstaHelper.b__31_0() 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 [HKLMSoftwareMicrosoftFusion!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 [HKLMSoftwareMicrosoftFusion!EnableLog]. End Error Error: 2021-01-21 20:52:01.92 Code: 0x00000003 Source: Create Voucher in ERP Description: The Script Task is corrupted. End Error Error: 2021-01-21 20:52:01.92 Code: 0xC0024107 Source: Create Voucher in ERP Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:52:01 PM Finished: 8:52:01 PM Elapsed: 0.5 seconds

Some of the thread talked about changing the project property Run64bitruntime to False , didn't work.

Some threads talked about reinstalling SQL Server and Visual Studio but I really don't want to do that as many of those talks about a lot of other errors pops up after reinstalling.

Please suggest what can be done to resolve this.

Thanks

question from:https://stackoverflow.com/questions/65839908/ssis-script-task-errors-out-when-executing-from-sql-server-agent-job-system-io

Answer 1

Translated - DLL of Version 15.0.0.0 of "Microsoft.VisualStudio.Tools.Applications" can not be found.

The message is handy, but it doesn't tell you if the 64/32 bit dll is missing. Some folks take a guess to switch between 32/64 mode and get lucky.

Check your Programs and Featured to confirm "Microsoft Visual Studion Tools for Application 2017" is installed.

Version 14.0 = VSTA 2015

Version 15.0 = VSTA 2017

Version 16.0 = VSTA 2019

Past Version 12, the vsta installer should install dlls for both 32/64 bit support.