SSIS script task fails on server with error "Cannot load script for execution"

Question

I have 3 SSIS packages with script tasks that all do very similar things - query a SQL Server database, loop through the results, and then call a SOAP webservice for each row. These packages run fine locally, and run fine on a development and a QA server.

On the production server, 1 runs fine and 2 fail with the ominous "Cannot load script for execution" error. These are SQL 2008 R2 environments on Server 2008 SP2. The error happens regardless of the user who runs them.

So far I have tried the following:

• Run the package in my dev environment with the database connections pointing to production (runs fine)
• Set Run64BitRuntime to false in project setting. Made no difference.
• Deleted the package and redeployed it
• Given explicit write access to /Windows/Temp as suggested here. This did initially happen when running with a proxy user, sowe tried this fix. But I get the same error when running with my admin account.

I cannot recompile the script on the production server because the development stack is not there and it is a production server. I can also not install or update BIDS on this server as proposed here.

It seems that somehow it might be required to recompile the script on the target environment, based on what some people have found, but I don't have this option.

Answer 1

So, the answer is unfortunately to rebuild the script task. It appears that these things can get corrupted. I literally did not change a single line of code, just recreated the task and copied the code from the broken version.

Answer 2

Can also be a version mismatch. If the SSIS project is set to target SQL Server 2012, but is deployed to a SQL 2016 instance then this error occurs. Change the target version in the project properties and redeploy.

Answer 3

Microsoft made a breaking change to ScriptTasks between SSIS2008R2 and SSIS2012. Thankfully, the fix is very simple. Just replace this line:

? 1 [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")] with this:

? 1 [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute] and everything should work for you as expect.

Answer 4

I had similar issue but in my case the target machine was missing a dll in the GAC for some SOAP web service calls using Microsoft Webservices3. Once I downloaded the install from Microsoft and installed on the target machine, issue resolved.