As per MSDN:
The dtexec command prompt utility is used to configure and execute SQL Server Integration Services packages. The dtexec utility provides access to all the package configuration and execution features, such as connections, properties, variables, logging, and progress indicators. The dtexec utility lets you load packages from three sources: a Microsoft SQL Server database, the SSIS service, and the file system.
/F[ile] filespec
(Optional). Loads a package that is saved in the file system. The filespec argument specifies the path and file name of the package. You can specify the path as either a Universal Naming Convention (UNC) path or a local path. If the path or file name specified in the filespec argument contains a space, you must put quotation marks around the filespec argument. The /File option cannot be used together with the /DTS or /SQL option. If multiple options are specified, dtexec fails.
Example Execution:
C:\Program Files\Microsoft SQL Server\130\DTS>dtexec /f "E:\*****.dtsx"
Microsoft (R) SQL Server Execute Package Utility
Version 13.0.1601.5 for 32-bit
Copyright (C) 2016 Microsoft. All rights reserved.
Started: 9:23:13 PM
Progress: 2017-11-24 21:23:14.24
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2017-11-24 21:23:14.29
Source: Data Flow Task
Validating: 50% complete
End Progress
Progress: 2017-11-24 21:23:14.29
Source: Data Flow Task
Validating: 100% complete
End Progress
Progress: 2017-11-24 21:23:14.45
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2017-11-24 21:23:14.47
Source: Data Flow Task
Validating: 50% complete
End Progress
Progress: 2017-11-24 21:23:14.47
Source: Data Flow Task
Validating: 100% complete
End Progress
Progress: 2017-11-24 21:23:14.47
Source: Data Flow Task
Prepare for Execute: 0% complete
End Progress
Progress: 2017-11-24 21:23:14.47
Source: Data Flow Task
Prepare for Execute: 50% complete
End Progress
Progress: 2017-11-24 21:23:14.47
Source: Data Flow Task
Prepare for Execute: 100% complete
End Progress
Progress: 2017-11-24 21:23:14.47
Source: Data Flow Task
Pre-Execute: 0% complete
End Progress
Progress: 2017-11-24 21:23:14.48
Source: Data Flow Task
Pre-Execute: 50% complete
End Progress
Progress: 2017-11-24 21:23:19.66
Source: Data Flow Task
Pre-Execute: 100% complete
End Progress
Progress: 2017-11-24 21:23:20.05
Source: Data Flow Task
Post Execute: 0% complete
End Progress
Progress: 2017-11-24 21:23:20.06
Source: Data Flow Task
Post Execute: 50% complete
End Progress
Progress: 2017-11-24 21:23:20.06
Source: Data Flow Task
Post Execute: 100% complete
End Progress
Progress: 2017-11-24 21:23:20.06
Source: Data Flow Task
Cleanup: 0% complete
End Progress
Progress: 2017-11-24 21:23:20.06
Source: Data Flow Task
Cleanup: 50% complete
End Progress
Progress: 2017-11-24 21:23:20.06
Source: Data Flow Task
Cleanup: 100% complete
End Progress
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 9:23:13 PM
Finished: 9:23:20 PM
Elapsed: 6.125 seconds
C:\Program Files\Microsoft SQL Server\130\DTS>
For DTSX in SQL Jobs, you can test using a new Proxy Account and execute the .DTSX step in the SQL Job as that Windows User. First create a new Credential and then a SQL Agent Proxy.
Creating SQL Server Agent Proxies
A SQL Server Agent proxy defines the security context for a job step. A proxy provides SQL Server Agent with access to the security credentials for a Microsoft Windows user. Each proxy can be associated with one or more subsystems. A job step that uses the proxy can access the specified subsystems by using the security context of the Windows user. Before SQL Server Agent runs a job step that uses a proxy, SQL Server Agent impersonates the credentials defined in the proxy, and then runs the job step by using that security context.
Many times if the SSIS Package is executing, but not progressing, a re-start of the SQL Agent may fix it.
Resources:
dtexec Utility (SSIS Tool)
Creating SQL Server Agent Proxies
|
|
|
|
|
|