CODEWITHSUNDEEP
ssissendmailonerror
ErickTreetops
ErickTreetops

Reputation: 3395

Calling Email OnError from ScriptTask of SSIS

I have a simple SSIS package with a single script task on it. I have an OnError event with a Send Mail task.

All i'm doing is testing to see if i can send an email when i detect an error withing my script. However the variable i use to hold the Error message for my email causes an error.

"A deadlock was detected while trying to lock variable "User::errMsg" for read access. "

Here is the very simple script

public void Main()
        {
            try
            {
                int x = 2;
                if (x ==2)
                {
                    throw new Exception("The number was  2");
                }

                Dts.TaskResult = (int)ScriptResults.Success;
            }

            catch(Exception e)
            {
                Dts.Variables["errMsg"].Value = e.Message;
                Dts.Events.FireError(-1, "here", "my error", "", 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }

How can i pass a message to my send email when an error occurs in my script task ?

Upvotes: 0

Views: 1419

Answers (1)

praveen
praveen

Reputation: 12291

SSIS variables are locked and are relased only in its Post Execute event .In your case errMsg variable gets locked by the script task and the same time the variable is accessed in On Error event by the SendMail component creating a deadlock situation .You need to specifically unlock the variable before it is accessed by other components during its execution phase.

Try this :-

 catch (Exception e)
        {
            Variables lockedVariables = null;
            Dts.VariableDispenser.LockOneForWrite("errMsg", ref lockedVariables);
            lockedVariables["errMsg"].Value = e.Message;
            lockedVariables.Unlock();


            Dts.Events.FireError(-1, "here", "my error", "", 0);
            Dts.TaskResult = (int)ScriptResults.Failure;

        }

This will ensure that before the error occurs ,the variable is unlocked and can be accessed in execution events such as On Error or OnExecStatusChanged.

More on how to handle these type of deadlocks is explained by Todd McDermid in his blog

Upvotes: 1

Related Questions