A few things here
When a script task/component doesn't work but opening it and resaving it fixes it, then something happened to SSIS package to wipe out the compiled bits in the package. When you have the Script's instance of Visual Studio open, when you compile/save/exit the script, behind the scenes what happens is the resulting assembly is serialized and stored into the SSIS package's XML.
Back in the 2005 era, the default behaviour was the Script itself was saved and then compiled and executed when needed but that introduced a host of other issues so now the script's bytes are saved into the package instead. Larger package now due the bits versus text but who cares about package size on disk at this point?
So, your task when this random incident occurs is to play Monsieur Lecoq and find out what happened to make the package change. Usual culprit is someone has deployed a newer version and broken yours but there a host of options here that made it go bad. Besides checking logs, etc, I would download/save the package to my local machine as Package.v1.dtsx. Copy paste that so I have Pacakge.v2.dtsx and open v2 in Visual Studio. Open the Script, Build, Save and then Save the package and look at the resulting .dtsx files in a text editor with a compare feature. Essentially what you're looking for is the Script Task's beginning tag and see what's in V1 which might be empty or mangled and compare that to what's in V2. It's all gonna be encoded binary but I'd at least confirm there is content in V1.
Another option is that there's a Version difference somewhere in the mix. The act of touching a package with a higher level of SSIS tooling can result in the binary getting updated to match the newer which presents a problem when it's deployed down a level. You build packages for SQL Server 2016. I deploy the packages using SSDT tooling for SQL Server 2017 to our shared SQL Server 2016 server. That deploy upgrades your packages in memory to 2017 which is then written to a 2016 instance and then bad things happen. But that's usually a different error. Versions can also be an issue when compatible updates happen. You target SQL 2014, I use SQL server 2016 Tooling to deploy to our 2016 and some times, deploy goes great but other times, especially if it's a Script Component that acts as a Source, the difference in version signature for the data flow can make things "weird."
Finally a good practice is to not have a MessageBox in your code. The reason for this, is that if the MessageBox attempts to fire when the package is running in an unattended state, no one can click the box to dismiss it so it will instead throw an exception. If you insist, then pass the System scoped variable InteractiveMode to your scripts. That Variable indicates whether SSIS can interact with the desktop aka show a message box.
if ((bool)this.Dts.Variables["System::InteractiveMode"].Value)
{
MessageBox.Show("My Message here");
}
I strongly prefer to raise Information events and use the following pattern. Add all the variables I care about as ReadOnly variables to my Script and then my Main looks like
public void Main()
{
bool fireAgain = false;
string message = "{0}::{1} : {2}";
foreach (var item in Dts.Variables)
{
Dts.Events.FireInformation(0, "SCR Echo Back", string.Format(message, item.Namespace, item.Name, item.Value), string.Empty, 0, ref fireAgain);
}
Dts.TaskResult = (int)ScriptResults.Success;
}
Now your data shows up in the Results tab and the Output window (where you can copy it) in Visual Studio execution and when you run it on the server, it will be in the SSISDB.catalog.operation_messages
