CODEWITHSUNDEEP
c#ssisscript-task
Arthur
Arthur

Reputation: 174

SSIS Script task fail, but works again when simply opening the script (no modification) and save it again. How come?

I'm developing SSIS packages on Visual Studio Community 2015 (version 14.0.25431.01 Update 3) with SSDT (version 14.0.61707.300) where I often use SSIS Script Tasks in C#.

My Packages are developed locally and then tested on various servers.

The problem I face is the following: sometimes, when moving the Package/Solution to another computer or simply when restarting my local VS, I launch the Package and it fails at the Script task step, with the commonly known "unreadable" error message.

enter image description here

Strange thing here is:

The weirdest thing is:

IT WORKS!!!

This wouldn't be too much of a problem if that random incident didn't happen every once in a while, even while in PROD...

Have you ever had this problem? Have you found a solution?

Note: this incident already happened on previous versions of SSDT, so don't look into that too much...

I can't even find similar problems in Google...

Upvotes: 0

Views: 1051

Answers (1)

billinkc
billinkc

Reputation: 61221

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

Upvotes: 1

Related Questions