10

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.

Improve this question
3
  • 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. Commented Mar 1, 2013 at 20:18
  • 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. Commented Mar 1, 2013 at 20:32
  • 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. Commented Mar 1, 2013 at 21:14

7 Answers 7

Reset to default
14

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Where do you change the target server version for a SSIS project?
Right click the project and click Properties, then Configuration Properties
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
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
I know this is old but will say that this was the case for the issue I was having
12

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.

Improve this answer

5 Comments

recreating the task changes the underlying XML metadata. It needs to recompile.
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.
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
Old question but recreating the script task did fix the erro.
+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!!
8

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.

Improve this answer

5 Comments

That fixed it for me. Thanks much @Ankit
My pleasure! Kindly mark this as answer if it helped.
Where do you make these changes?
@Hotelsinger - I made these changes in Script editor.
Already have this attribute in script but still getting the error :(
1

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.

Improve this answer

Comments

1

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}".

Improve this answer

Comments

0

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.

Improve this answer

Comments

0

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).

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.