The following example shows how to run the same package and capture the return code:
DECLARE @returncode int
EXEC @returncode = xp_cmdshell 'dtexec /f "C:\UpsertData.dtsx"'
Ex:/DTS "\MSDB STORM\Archibus_oracle_encumbrance" /SERVER "." /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
(Optional). Loads a package from the SSIS Package Store. The package_path argument specifies the relative path of the SSIS package, starting at the root of the SSIS Package Store, and includes the name of the SSIS package. If the path or file name specified in the package_path argument contains a space, you must put quotation marks around the package_path argument.
The /DTS option cannot be used together with the /File or /SQL option. If multiple options are specified, dtexec fails.
(Optional). When the /SQL or /DTS option is specified, this option specifies the name of the server from which to retrieve the package. If you omit the /Server option and the /SQL or /DTS option is specified, package execution is tried against the local server. The server_instance value may be quoted.
(Optional). Specifies the number of executable files that the package can run concurrently. The value specified must be either a non-negative integer, or -1. A value of -1 means that SSIS will allow a maximum number of concurrently running executables that is equal to the total number of processors on the computer executing the package, plus two.
(Optional). Sets a value that determines whether the package will use checkpoints during package execution. The value on specifies that a failed package is to be rerun. When the failed package is rerun, the run-time engine uses the checkpoint file to restart the package from the point of failure.
The default value is on if the option is declared without a value. Package execution will fail if the value is set to on and the checkpoint file cannot be found. If this option is not specified, the value set in the package is retained. For more information, see Using Checkpoints in Packages.
The /CheckPointing on option of dtexec is equivalent to setting the SaveCheckpoints property of the package to True, and the CheckpointUsage property to Always.