SSIS Executing Packages
SSIS Package Execution Methods
This tutorial describes several different methods of executing SSIS Packages: DTEXEC command, DTEXECUI user interface from SSMS Integration Services Connection and xp_cmdshell in T-SQL.
DTEXEC in the Command Prompt
DTEXEC - Open a command prompt window. For help, type DTEXEC /? to view all of the switches that can be used with the utility.
The syntax for a package named "Package" located on the SQL Server is:
DTEXEC /SQL "\Package" /SERVER "SQLSERVER2008R2"
The syntax for a package in the File System is:
DTEXEC /F "C:\pathtofile\Package1.dtsx"
This particular example takes a variable called myString as an argument.
DTEXECUI or the Execute SSIS Package Utility
DTEXECUI - This utility can be accessed by typing DTEXECUI from the command prompt or from the Start, Run box or from SSMS by opening an Object Explorer Connection to Integration Services and Right Clicking on a Stored Package and selecting Run Package.
Variables can be set from this utility.
The package progress will display.
SQL Server call using xp_cmdshell - The DTEXEC command line utility can be called from a Query window. This call is to Package1 on the File System and takes a variable called myString. It uses the same arguments as the command line utility.
Declare @cmd varchar(2500)
Declare @i int
SET @cmd =
'DTEXEC /FILE "C:\Data\SiteData\Package1.dtsx" /SET "\Package.Variables[User::myString].Properties[Value]";"AdventureWorks" '
EXEC @i = master..xp_cmdshell @cmd
SQL Server Jobs for Package Execution
There are a variety of ways to execute SSIS packages via command line or utility. Packages can be easily scheduled to run as SQL Server jobs on a daily, weekly, monthly, etc. basis.
DTEXEC Notes About 32 vs 64 bit Systems
I had an issue with my 64 bit SQL Server 2008R2 running on a Windows 2008 server that I needed to run an SSIS package on that took data from an OLE Data source, did a Data Conversion on, and finally pushed it out to MS Excel. I created a stored procedure on this server and instance of SQL Server only to have it fail with a conflict error message telling me that 64 bit SQL Server was not compatible with the 32 bit instance of Excel. I correctly configured the SSIS project properties for Debugging to Run64BitRuntime = False and had it running on my local machine, but once deployed to a different environment, it was failing. The server had 2 DTEXECs - one 64 bit (the Default one) and a 32 bit one in the C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn folder. FYI, when calling the full path for the 32-bit DTEXEC in the stored procedure, place quotes around the entire path:
'"C:Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTEXEC.exe" /F Package.dtsx'
If you forget, SQL Server will provide a nice error message stating:
'C:\Program' is not recognized as an internal or external command,operable program or batch file. .