How long does it take for SSISDB to record package execution status?

Question

I am running a series of SSIS packages using DTEXEC in Powershell. The packages reside in SSISDB.

I have no trouble running a package, but I am running into problems determining the actual result status once the package has completed. When a package is run from SSISDB, DTEXEC appears to return a ZERO return code even when the package fails (e.g. file not found during task validation).

I have tried to query SSISDB (catalog.executions) to check the status once DTEXEC has completed (or I think it has completed). I can get back a status 2 ("running"). This even occurs when I add a 5-10 second wait.

I suspect that the code I am using to run DTEXEC may be the culprit. This is the function I am using:

function run_pkg ($DTExecArgs) {
    $rc = -1
    # Run DTExec
    $pinfo = New-Object System.Diagnostics.ProcessStartInfo
    $pinfo.Filename = "C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe"

    write-host "Starting... " $DTExecArgs
    # The next few lines are required to make sure the process waits for
    # the package execution to finish
    $pinfo.RedirectStandardOutput = $true
    $pinfo.UseShellExecute = $false
    $pinfo.Arguments = $DTExecArgs
    $p = New-Object System.Diagnostics.Process
    $p.StartInfo = $pinfo

    $p.Start() | Out-Null
    $output = $p.StandardOutput.ReadToEnd()
    $p.WaitForExit()
    $rc = $p.ExitCode
    # DTExec Finished
    return $rc
}

The argument to the function looks like:

/isserver \SSISDB\IPTS-DW\ETL\ETL_SYSTYPE_T_PKG.dtsx /server localhost

I think that the WaitForExit() should cause the script to wait until DTEXEC completes.

Any ideas? Does DTEXEC throw the work over the fence to ICS and then exit? I am doing something wrong?

Thanks

Answer 1

Try passing the Synchronized flag to the dtexec

/Par "$ServerOption::SYNCHRONIZED(Boolean)";True