BI Admin Scripts: Run DTExec with PowerShell

There are a few different ways to run SSIS packages. You can use the DTExec utility. You can create a script that runs it the package via the object model. I think there are some others that I can't remember at the moment too. My scenario required a script that could run a package whether it was built in SSIS 2008 R2 or 2012. Additionally, the 2012 packages could be part of a Package Deployment Model project or a Project Deployment Model project. Read on for a script that covers everything I could think of (so far).

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!