Before you get to far into this, you'll notice a reference to a script called GetEnvironmentReferenceId.ps1. You'll need to refer pack to last week's post titled SSIS 2012 Gotcha: DTExec.exe and the Environment Reference ID to get this script.
The first trick to this script is determining if we're running a Project- or a Package-deployed package. If the package path tells us this since the Project-deployed packages all start with the "\SSISDB" in the path. If we're dealing with a Project-deployed scenario we have to specify the the configuration environment as well as add a parameter that tells the package to maintain synchronization with the client. This last part is important as the DTExec will kick off the package and quit unless we tell it very explicitly that we want it to wait and tell us how things went. There is also some special code in the section that starts the DTExec process that makes sure the package completes before the process bails on it.
Finally, we have a special case that you might be able to remove from your script if you don't need it. In our case, we have a third-party scheduler that accepts any exit code of zero or less as a success. We ran into some cases where the DTExec return code was a negative value. Our scheduler thought that was just fine and failed to report the failure.
Set-Location "C:\SSISScripts"
CLS
. .\GetEnvironmentReferenceId.ps1
# Determine which deployment model was used for this package
# and set the DTExec arguments appropriately
If ($PackagePath.SubString(0, 7) -eq "\SSISDB")
{
$EnvironmentReferenceId = Get-EnvironmentReferenceId $Environment $PackagePath
$DTExecArgs = "/ISSERVER ""$PackagePath"" /Env $EnvironmentReferenceId /CHECKPOINTING OFF /REPORTING EW /Par """"`$ServerOption::SYNCHRONIZED(Boolean)"";True"""
}
Else
{
$DTExecArgs = "/DTS ""$PackagePath"" /CHECKPOINTING OFF /REPORTING EW"
}
Get-Location
# Run DTExec
$pinfo = New-Object System.Diagnostics.ProcessStartInfo
If ($Architecture -eq "64")
{
$pinfo.FileName = "C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe"
}
Else
{
$pinfo.FileName = "C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\DTExec.exe"
}
$pinfo.FileName # Output the DTExec path and filename
$DTExecArgs # Output the DTExecArgs variable
# 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()
$DTExecExitCode = $p.ExitCode
$output
# DTExec Finished
# This If/Else block is for our third-party scheduler that thinks
# that negative return codes are equivalent to "success"
If ($DTExecExitCode -ge 0)
{
$Result = $DTExecExitCode
}
Else
{
$Result = 0 - $DTExecExitCode
}
Write-Output "Return Code = $Result"
Exit $Result
Got some other ideas on how to handle this? I would love to see them so please leave a comment!
Hi Bryant,
ReplyDelete1st your blog and articles look very great!
I have a question about how to call the above Powerwhell.
Which arguments you need to pass ?
Thanks for your help,
Dan