How to call cmd.exe from SSIS and execute commands on the command line

Question

I am using a Script Task in SQL Server Integration Services (SSIS 2008) to run a command on the Windows command line. The command simply decrypts a file with a known filename.

When I execute the task, the Script task box turns green and reports that it ran successfully, but when checking on the file, I see that nothing at all has happened. Below is my code. Any idea what I'm doing wrong here? Thank you in advance.

Update: My goal is to decrypt a file from a C# SSIS script, and to learn why the code below doesn't do that. I'm not as concerned with whether or not the task box turns green or reports a success.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Diagnostics;


    public void Main()
    {
        string DecryptCommand;

        var startInfo = new ProcessStartInfo
        {
            FileName = "cmd.exe",
            RedirectStandardInput = true,
            RedirectStandardOutput = true,
            UseShellExecute = false,
            CreateNoWindow = true
        };

        DecryptCommand = "echo my_passphrase|gpg --batch --passphrase-fd 0 --decrypt-files      my_file_" + DateTime.Now.ToString("MMddyyyy") + ".pgp"; 
        var process = new Process { StartInfo = startInfo };

        process.Start();
        process.StandardInput.WriteLine("cd D:\\Foo\\Bar\\EncryptedFiles");
        process.StandardInput.WriteLine(DecryptCommand);
        process.StandardInput.WriteLine("exit");
        process.WaitForExit();

    }
}

}

Answer 1

Dts.TaskResult = (int)ScriptResults.Success;

This line means you will always return true when the process exits, regardless of what the process returned. Instead you likely want to capture the process' exit code (even better, log its general output while debugging) and use that to evaluate whether or not your script succeeded.

Going a step further, is there a reason you are using command.exe rather than executing a batch or vb script? That would give you more control over your output.

Edit: As for why your program isn't executing as intended, have you tried manually flushing your streams after inputs?