SSIS Job does not run my .exe file and job executes successful.

Harms, Timothy 5 Reputation points
2023-03-09T21:24:18.7633333+00:00

Hello,
	SSIS Job deployment does not run my .exe and job executes successful but does not do what it is supposed to do. I have built-in logging in the application which does not do any logging.
	I have scripted a simple script task in c# to run an .exe file with the following code:

		public void Main()
		{
            string executionId = Dts.Variables["User::ExecutionId"].Value.ToString();
            string workingDirectory = Dts.Variables["User::WorkingDirectory"].Value.ToString();
            string executable = Dts.Variables["User::Executable"].Value.ToString();
            bool fireAgain = false;

            System.Diagnostics.Process process = new System.Diagnostics.Process();

            process.StartInfo = new System.Diagnostics.ProcessStartInfo(workingDirectory + executable, executionId);
            process.StartInfo.WorkingDirectory = workingDirectory;

            process.StartInfo.UseShellExecute = false;
            Dts.Events.FireInformation(3, "Script Task", "Start", "", 0, ref fireAgain);
            // execute 12_999 Application
            try
            {
                //Process.Start(workingDirectory + executable, executionId);
                Dts.Events.FireInformation(3, "Process Task", "Start", "", 0, ref fireAgain);
                process.Start();
                Dts.Events.FireInformation(3, "Process Task", "End", "", 0, ref fireAgain);
            }
            catch (Exception e)
            {
                Dts.Events.FireError(0, "Script Task", "Exception encountered: " + e.ToString(), String.Empty, 0);
            }

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

I have also tried using the Execute Process task in the SSIS toolbox with the same result.

When I debug the package on my laptop it executes the .exe file as expected. 

I have the package logging but does not have any errors.

When deployed to the SQL server as a job, the job executes as a success but my program does not run?

The .exe file is from Visual Studio in C# and is the Inteligenz/X12Parser from GitHub and is what I am trying to execute.
Many thanks!

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,449 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,199 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Michael Taylor 47,711 Reputation points
    2023-03-09T22:35:36.68+00:00

    It probably is running but your script isn't waiting for it to complete. Calling Start simply triggers the OS to create and run the process. It doesn't wait around for the process to finish. To wait for the process to finish you need to use WaitForExit or similar.

    Dts.Events.FireInformation(3, "Process Task", "Start", "", 0, ref fireAgain);
    process.Start();
    
    //Wait for it...
    process.WaitForExit();
    
    Dts.Events.FireInformation(3, "Process Task", "End", "", 0, ref fireAgain);
    

    WaitForExit allows a timeout if you want to block for a while and kill the process if it takes too long. After the process has completed you can use ExitCode to get the return value from the process. Outside of a bad path or invalid process start options then Start will almost never fail. Errors that occur in the other process are not accessible in the script unless you also hook into the process'es output stream but that gets hard. The exit code is the closest indicator that you may have of whether the process succeeded or not.

    You should also wrap your usage of Process in a using statement to ensure it gets cleaned up.

    1 person found this answer helpful.
    0 comments No comments