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

0
votes

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 [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: 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

1
sql-servervisual-studiossissql-agent-job
1) Are you sure VS 2012 is completely compatible with SQL Server 2019? 2) How do you deploy your packages, and to where? – Roger Wolf
@RogerWolf , you are right. I installed the VS2019 Professional , Open a new project in 2019 , added all my 2012 packages in the new project which upgraded all of them. I did see warnings about script task needs to be migrated but all of them started working as expected. Sql jobs started executing all the packages with/without script tasks.Thank you. – AnkitThakkar

1 Answers

1
votes

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.