1

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

James Page
  • 91
  • 1
  • 3

1 Answers1

1

Try passing the Synchronized flag to the dtexec

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

billinkc
  • 59,250
  • 9
  • 102
  • 159
  • Thanks - this works. I also found some info under http://msdn.microsoft.com/en-us/library/hh231187.aspx#parameter – James Page Jul 25 '13 at 20:02
  • Glad to hear it and welcome to SO. If you find questions or answers useful, feel free to click the Up arrow. If this solves your issue, you may click the green check mark to set it as resolved – billinkc Jul 25 '13 at 20:35