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.
:{>
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