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.

Gas

It does not matter if it runs as an agent job or directly with the execute package utility. Same error. The property is set to 32bit, though. – Gas

I appreciate your effort, but the script does not compile/load, so this is not a runtime issue. It has to do with security or the environment. The web service is accessed just fine by the one package that runs. – Gas

I don't know the patch levels. The configuration is all database-driven and is the same for all packages. ANd btw, I just recreated the script task in one of the packages that failed, copied all the code over, redeployed, and voila, it works. Beats me what was going on. – Gas

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.

Gas

recreating the task changes the underlying XML metadata. It needs to recompile. – Samaria

Wouldn't running "Build" from within the script project also change the XML metadata? That did not have the intended effect. I suppose it did not change the medatadata quite "enough". Recompiling the (C#) script project and redeploying the package did not help. – Gas

I was getting the DTS Script Task: Runtime Error :: Exception has been thrown by the target of an invocation After spending hours trying to figure out what the problem was and while trying to debug, I then came across the error above as well. This solution solved both problems – Saideman

Thank you, this helped! – Carpogonium

Old question but recreating the script task did fix the erro. – Manara

+1 With SSIS this fix is pretty much like how you fix Many Windows problems - "did you reboot?" These components just get corrupted and bad things happen. If something just stops working and there is no explanation, just delete it and rebuild it. Thanks for the reminder!! – Balky

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.

Leaper

Where do you change the target server version for a SSIS project? – Kilmarx

Right click the project and click Properties, then Configuration Properties – Leaper

I was developing the packages in VS2019 and the default target was SQL2019. Changing the target back to SQL 2017 (where I was deploying the jobs) worked for me. Thanks – Lulalulea

Thanks for this, I was deploying to Azure, and when I checked the project properties, it was to to SQL2019 with Azure Enabled = FALSE. Changing that to true (which also changed the version down to 2017) has immediately fixed the problem – Thamora

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.

Bresnahan

That fixed it for me. Thanks much @Bresnahan – Elwandaelwee

My pleasure! Kindly mark this as answer if it helped. – Bresnahan

Where do you make these changes? – Parrie

@Parrie - I made these changes in Script editor. – Bresnahan

Already have this attribute in script but still getting the error :( – Carpogonium

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.

Helga

Answer 5

I find that the error also pops up if your C# script is built against older version of SQL but contains new C# syntax such as ternary operator "?: ??" or string interpolation $"{varaible}".

Shugart

Answer 6

On Visual Studio 2019, I had to rebuild the whole package, not just the script task. I did this by copying and pasting all package tasks, variables, and parameters into a new one.

Syne

Answer 7

As Larry pointed out, it is worth stressing that if you are using custom dlls within the script and these have not been properly loaded into the GAC this can also result in this error (as I spent a week discovering).

Trovillion