Search code examples
c#sql-serverssiswinscp-net

SSIS Script task: FireError() hangs in Catch block


Using SQL Server 2014. I have a script task, that downloads files via WinSCPnet.dll assembly, where I am attempting to log errors to the SSIS log via a FireError() within the catch block. Unfortunately, when the program reaches the FireError(), it just hangs without failing or without any message. I have followed the example provided on the MSDN (see code below) without success. Can anyone please help?

Note: I have a FireInformation() event that works upon successful download.

https://learn.microsoft.com/en-us/sql/integration-services/extending-packages-scripting/task/logging-in-the-script-task?view=sql-server-2014

    public void Main()
    {
        // Set variable declaration(s).            
        string strTaskName = (string)Dts.Variables["System::TaskName"].Value; // Has been selected as read-only.

        SessionOptions sessionOptions = new SessionOptions
        {
            // Set SessionOptions here
        };

        try
        {
            using (Session session = new Session())
            {
                // Create error here by downloading a file from a folder path that does not exist.

                foreach (TransferEventArgs transfer in transferOperationResult.Transfers)
                {
                    // Print results to SSIS log. The FireInformation() works here.
                    Dts.Events.FireInformation(0, string.Format("Script Task {0}", strTaskName), string.Format("Download of file {0} succeeded.", transfer.FileName), null, 0, ref fireAgain);
                }
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }
        catch (Exception ex)
        {                
            // Print results to SSIS log. The program hangs here without message.
            Dts.Events.FireError(0, string.Format("Script Task {0}", strTaskName), ex.Message, null, 0);

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

Solution

  • Found the problem. I have an OnError Event Handler setup to send an e-mail upon failure of any task. The problem stemmed from the fact that the Send Mail Task had the wrong connection manager selected. I found the problem by literally running every task (i.e., right click + Execute Task) on the logic chain in order to find the problem and ended at the Send Mail Task where it told me the connection manager could not be found.

    It is odd that the SSIS debugger did not report the same error in the Execution Results when trying to run the entire package. I am not aware of that being normal/expected behavior. Perhaps there is a bug between the Script Task, On Error Event Handler and the Send Mail Task?