SSIS C# Script Task for automation

Question

I have an SSIS package that begins with a script task to check that a specified directory contains three files. If there are 2 files it prints a warning, if there are 1 or less files I want to print an error message and terminate the program. The idea is that SQL Agent will register my package and execute it at a specified time. However the following conditions in my script must be met:

switch (numFiles)
            {
                case 0:
                    MessageBox.Show("Error: No files are in the directory C:\\Directory1\n Please restart execution.");
                    break;
                case 1:
                    MessageBox.Show("Error: Only one file was found in the directory C:\\Directory1\n Please restart execution.");
                    break;
                case 2:
                    MessageBox.Show("Warning: Only two files have been loaded into the directory C:\\Directory1\n Is this intended?.");
                    break;
            }

How do I gracefully terminate the program and print the warning messages? How do I show this message when it is being run on a server?

Answer 1

I think you'll be best served by hooking into the existing Events. Using your code as a baseline, I added a variable into a package and then deployed into SQL Server.

        int numFiles = Convert.ToInt32(Dts.Variables["numFiles"].Value.ToString());
        switch (numFiles)
        {
            case 0:
                //MessageBox.Show("Error: No files are in the directory C:\\Directory1\n Please restart execution.");
                Dts.Events.FireError(0, "File count", "Error: No files are in the directory C:\\Directory1\n Please restart execution.", string.Empty, 0);
                break;
            case 1:
                //MessageBox.Show("Error: Only one file was found in the directory C:\\Directory1\n Please restart execution.");
                Dts.Events.FireError(0, "File count", "Error: Only one file was found in the directory C:\\Directory1\n Please restart execution.", string.Empty, 0);
                break;
            case 2:
                //MessageBox.Show("Warning: Only two files have been loaded into the directory C:\\Directory1\n Is this intended?.");
                Dts.Events.FireWarning(0, "File count", "Warning: Only two files have been loaded into the directory C:\\Directory1\n Is this intended?.", string.Empty, 0);
                break;
        }

Running from the command line

"DTExec.exe" /file /set .\so_RaiseEvents.dtsx /set \Package.Variables[User::numFiles];0

"DTExec.exe" /file /set .\so_RaiseEvents.dtsx /set \Package.Variables[User::numFiles];1

"DTExec.exe" /file /set .\so_RaiseEvents.dtsx /set \Package.Variables[User::numFiles];2

I see the expected output

Error: 2012-08-09 08:53:58.77
   Code: 0x00000000
   Source: Script Task File count
   Description: Error: No files are in the directory C:\Directory1
 Please restart execution.
End Error

Error: 2012-08-09 08:51:56.75
   Code: 0x00000000
   Source: Script Task File count
   Description: Error: Only one file was found in the directory C:\Directory1
 Please restart execution.
End Error

Warning: 2012-08-09 08:51:51.82
   Code: 0x00000000
   Source: Script Task File count
   Description: Warning: Only two files have been loaded into the directory C:\Directory1
 Is this intended?.
End Warning

Warning should show up when you run from agent but if it doesn't, then you'd need to add a parameter to reporting so /report W

edit

And to address these points

"How do I gracefully terminate the program and print the warning messages" The FireError will cause the Script task to return a failure code Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, thus ending the package execution. The Warning, well if you want to stop package execution then you would modify the script task to indicate it did not Succeed. I did this by adding a third ScriptResult enumeration of Warning which is translated to a DTSExecResult.Canceled (only other option that conveyed something didn't go to plan). Full code is below

[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{


    /// <summary>
    /// This method is called when this script task executes in the control flow.
    /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    /// To open Help, press F1.
    /// </summary>
    public void Main()
    {
        int numFiles = Convert.ToInt32(Dts.Variables["numFiles"].Value.ToString());
        switch (numFiles)
        {
            case 0:
                //MessageBox.Show("Error: No files are in the directory C:\\Directory1\n Please restart execution.");
                Dts.Events.FireError(0, "File count", "Error: No files are in the directory C:\\Directory1\n Please restart execution.", string.Empty, 0);
                break;
            case 1:
                //MessageBox.Show("Error: Only one file was found in the directory C:\\Directory1\n Please restart execution.");
                Dts.Events.FireError(0, "File count", "Error: Only one file was found in the directory C:\\Directory1\n Please restart execution.", string.Empty, 0);
                break;
            case 2:
                //MessageBox.Show("Warning: Only two files have been loaded into the directory C:\\Directory1\n Is this intended?.");
                Dts.Events.FireWarning(0, "File count", "Warning: Only two files have been loaded into the directory C:\\Directory1\n Is this intended?.", string.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Warning;
                break;
            default:
                Dts.TaskResult = (int)ScriptResults.Success;
                break;
        }

    }

    /// <summary>
    /// This enum provides a convenient shorthand within the scope of this class for setting the
    /// result of the script.
    /// 
    /// This code was generated automatically.
    /// </summary>
    enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure,
        Warning = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Canceled,
    };

}

It seems like you are expecting someone to be watching and responding to a package's run. Generally, that's not a thing you will want SSIS to be doing. It can do these things, you could have a messagebox prompt to discover whether the user wishes to continue but when it's run on the server, the package will fail validation checks as it will discover it is not running in interactive mode. This is far better than DTS when people would leave message boxes up on server and packages would hang for weeks as no one was regularly logged into the server. If you need to serve both masters (attended and unattended run), then use the System::InteractiveMode variable so you do not attempt to display UI code during an unattended run.

A better option in my mind, would be to keep the Event's firing as above so it works great in an automated environment. Then for your manual executions, provide a lightweight .NET wrapper for running the job. Offload the file checks to that too. Yes, work is repeated but you'll have a cleaner separation of ETL and UI code.

Answer 2

You can't show a message box like that when running on the server.

You should write to a log file (http://msdn.microsoft.com/en-us/library/ms167456%28v=sql.105%29.aspx).

In order to stop the package running from script task use RunningPackage.Stop method

        static void Main(string[] args)
        {
            Application app = new Application();
            RunningPackages pkgs = app.GetRunningPackages("yourserver");

            int pkgsRunning = pkgs.Count;
            Console.WriteLine("Packages before stop: thas + pkgsRunning);

            foreach (RunningPackage p in pkgs)
            {
                Console.WriteLine("InstanceID: " + p.InstanceID);
                Console.WriteLine("PackageDescription: " + p.PackageDescription);
                Console.WriteLine("PackageID: " + p.PackageID);
                Console.WriteLine("PackageName: " + p.PackageName);
                Console.WriteLine("UserName: " + p.UserName);
            }

            pkgs = app.GetRunningPackages("yourserver");
            foreach (RunningPackage package in pkgs)
            {
                package.Stop();
            }

            pkgsRunning = app.GetRunningPackages("yourserver").Count;
            Console.WriteLine("Packages after stop " + pkgsRunning);
        }

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.runningpackage.stop.aspx