How to fail a SSIS Script Task (using C#, 2008)

Question

I have seen many codes lying around but apparently they don't help.

Here is the logic I have to follow:

I have a file getting read which has an amount field. This amount field can have negatives. I have to move the file and fail the package if I find a negative amount in the file.

What I did so far:

Added a script task to get file path, added loop to read all files in that path, inside the loop(added a DTS task to move members with negative amounts to a record set, added script task to check wether there are any rows in the recordset, if count > 0 then FAIL the script task thus failing the Package, else pass)

The scipt task's code:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;
using System.Xml;

// name space call here etc, etc..
enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
public void Main()
        {
            // TODO: Add your code here

            bool fireAgain = true;
            DataTable dtable = new DataTable();
            OleDbDataAdapter da = new OleDbDataAdapter();

            // Setting this incase there are multiple files being checked
            Dts.Variables["InvalidFile"].Value = "False";


            da.Fill(dtable, Dts.Variables["NegativeAmountMember"].Value);

            // I do get my desired result here of 1    
            Dts.Events.FireInformation(0, "dtable", dtable.Rows.Count.ToString(), String.Empty, 0, ref fireAgain);

            if (dtable.Rows.Count > 0)
            {
                Dts.Variables["InvalidFile"].Value = "True";
                Dts.Events.FireInformation(0, "InvalidFile", Dts.Variables["InvalidFile"].Value.ToString(), String.Empty, 0, ref fireAgain); // This does get displayed but, nothing happens after this step.
                Dts.TaskResult = (int)ScriptResults.Failure; // this method never technically executes
                return; // added this because someone somewhere said in a post to do so
            }
            else
            {
                Dts.TaskResult = (int)ScriptResults.Success;
            }
        }

I have everything working, except failing this Script Task.

I also tried FireError, even that does not get called.

What is happening, can anyone explain please?

Answer 1

In SSIS, failure doesn't automatically stop execution (as you've found out). Failure is a state, indicating that something went wrong. The fact that execution doesn't suddenly stop on failure allows you to orchestrate graceful error handling. For example, you might want to perform remedial action when a failure occurs.

The below code snip from your question sets the task's result to failure (line 1) and ends execution of the task (line 2).

Dts.TaskResult = (int)ScriptResults.Failure;
return;

If the task isn't failing/execution isn't stopping, then the problem almost certainly is related to the if condition controlling whether the above code executes (if (dtable.Rows.Count > 0))--somehow this condition isn't evaluating to true when you expect it to.

It's also possible that the Script Task is failing but just doesn't look like it. What may be happening is that the Script Task encounters an error and fails and then the loop starts a new iteration and runs the Script Task for the next iteration. The rapid fail -> next iteration -> next Script Task execution might make it look like the Script Task stays in the running state after failure.

If you want the fact that the Script Task fails to prevent the loop from further iteration, you'll somehow need to switch the loop's ExecutionResult to failure. You could do this by having your Script Task FailParentOnFailure or by setting the loop's MaximumErrorCount property low enough so that the error raised by the Script Task's failure causes the loop's MaximumErrorCount threshold to be met.

Answer 2

Guys I figuered out the problem. The issue was a deadlock in the code with the InvalidFile variable.

Added:

    Variables lockedVariables = null;
    Dts.VariableDispenser.LockOneForWrite("InvalidFile", ref lockedVariables);
    lockedVariables["InvalidFile"].Value = "True"; 
    lockedVariables.Unlock();

to the code and the issue is now gone. With regards to @Ben Gribaudo's comment on invalid loop, the loop was valid, my comments in the code suggest such.

The deadlocked variable was also being used in an onError task where an email gets sent out. Because of the variable being locked in the loop for a write and the script task never actually failing until the onError is completed, the variable stayed locked. After adding the lock for one write code, it all worked.

Thank you for your help, explanations and answers.

Answer 3

Exactly what I was looking for, exit if a particular call to a SPROC returns a SQL error on my catch.

  catch (SqlException ex)
                {
                    ExceptionLogging.SendExcepToDB(ex);
                    Dts.TaskResult = (int)ScriptResults.Failure;
                    return;
                } 

Answer 4

I don't have a good enough "reputation" so I have to post an answer... Lock variables will prevent event handlers on your failure from working!

So before you fail a script component, release the variables is a good practice.

More straight forward code to unlock all in catch statement simply do:

Dts.Variables.Unlock();

There is no need to establish a new variables object to unlock them.