If I had a nickel for every time I’ve encountered “Cannot load script for execution,” I’d have a lot of nickels.
In this short (7:24) video, I demonstrate the issue and my fix:
My Fix
For me, the fix is almost always setting the SSIS project’s TargetServerVersion property to the proper version:
“Andy, How Did I Get Here?” </TalkingHeads>
That is an excellent question. I’m glad you asked!
On my server, I installed SQL Server 2017. I also installed the latest version of SQL Server Management Studio even though it no longer supports Debugging (even though some DBAs never use debugging and don’t understand those of us who do… well, did use debugging in SSMS…) </miniRant1>.
When I installed the latest version of SSMS (v18.9.1 in this case), I also installed SQL Server 2019 components. Now, I get why SQL Server 2019 components are installed with SSMS; SSMS supports SQL Server 2019.
I wish the SSIS template did not default to the latest version of SQL Server it finds installed. I’d like an SSMS Debug button. And I’d also like a Visual Studio Business Intelligence option to set the default TargetServerVersion property – perhaps on this VS Options page (unless experienced DBAs never use it. In that case, it’s not a valid request, is it??) </miniRant2>:
If you haven’t checked out the “Skip validation when opening a package” functionality (shown in the image above), this is extremely handy. I don’t have as much time to grab coffee refills while waiting for packages to open, though. Confession: I still go grab coffee because of habit.
Conclusion
If you receive the “Cannot load script for execution” error, a good place to begin troubleshooting is: Verify the SSIS project TargetServerVersion property.
:{>
Learn More
All of my SSIS (and ADF, Azure, and Fabric) content is available as part of Premium Level, which you may access for one full year for one low price!
Thanks for this, it worked like a charm.
Perfect!! I would have never found that!
I followed the given steps. I still facing the same issue. Let me know if there is anything else can be done.
Hi Chandra,
I recommend you start the SQL Server Data Tools installer and select Repair when prompted to Repair, Uninstall, or Modify.
Hope this helps,
Andy
Thank you! Your solution hit the nail on the head.
Awesome! Glad I could help. :{>
We have migrated from SQL server 2012 to SQL Server 2019. While executing SSIS package we are getting script can not be loaded. We have recreated the task by copy and paste, rebuild the script task but still we are facing the issue. appreciated your help.
What if my SSMS version is 19 (19.2 to be precise) as it is mentioned in the default?
Hi Karina,
If you are seeing a script error, check the TargetServerVersion project property, as mentioned in the post.
The script error usually indicates the SSIS package contains a version of a .Net language that is not supported on the SSIS Catalog in which the package is attempting to execute.
Hope this helps,
Andy
I am facing the error you mentioned and I also followed the step but it didn’t help on fixing. My error is dts script task has encountered an exception in user code cannot load script for execution. It was all working, but I didn’t change anything, and all the sudden it cannot perform any script task to check if a file exist if so then import if not then exit. I can still import the files but the whole process can’t be automated anymore. Please see the below script FYI:
public void Main()
{
string path = Dts.Variables[“FileName”].Value.ToString();
Dts.Variables[“DoesFileExist”].Value = File.Exists(path);
//Dts.Variables[“FileName”].Value = System.IO.Path.GetFileName(Dts.Variables[“FilePath”].Value.ToString());
Dts.TaskResult = (int)ScriptResults.Success;
}
Do you have any insight you can provide? Thanks in advance
Thanks for reading my blog and for reaching out, Jacky!
It’s possible you are encountering a different error. I found the following article that may help: https://learn.microsoft.com/en-us/answers/questions/158606/error-dts-script-task-has-encountered-an-exception
Hope this helps,
Andy
I have an SSIS package. A custom component is used in Event Handler The custom component solution file is building without an error. When running the main SSIS package, it is throwing an error saying “The task has failed to load. There were errors during task validation”. I am getting the above error in both OnPreExecute and OnPostExecute when i see in the Progress tab. Could you throw some light on this issue
Hi Vishy,
Have you connected your Visual Studio custom task project to an instance of Visual Studio in which you are testing your custom task? That will allow you to break execution and step through the code. This is almost magic when debugging errors such as the one you describe.
I checked my book, Building Custom Tasks for SQL Server Integration Services, 2nd Edition (https://www.amazon.com/Building-Custom-Server-Integration-Services-ebook/dp/B08WWS24CR) for a section explaining how to accomplish this kind of debugging and I’m astonished that I cannot find a walk-through on troubleshooting by attaching to a second instance of Visual Studio. Maybe it’s there and I am just missing it. If I write an updated edition, I will have to include this!
You can find an excellent description of the sort of troubleshooting I describe above in Kirk Haselden’s book Microsoft SQL Server 2005 Integration Services (https://www.amazon.com/Microsoft-Server-2005-Integration-Services/dp/0672327813) in chapter 24: Building Custom Tasks, starting on page 615. Kirk was kind enough to write the foreword to my custom tasks 2nd edition, and he was most kind. When I looked up his book just now, it was available used on Amazon for $9USD. It is well worth that price. Not only does Kirk cover custom task development, he also covers custom Data Flow component development.
Hope this helps,
Andy