Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


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.

DTEXEC Command Help

The syntax for a package named "Package" located on the SQL Server is:

DTEXEC /SQL "\Package" /SERVER "SQLSERVER2008R2"

SSIS DTEXEC Sample

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.

DTEXEC File System Sample

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.

SSMS Packages SSIS DTEXECUI

Variables can be set from this utility.

SSIS Variables

The package progress will display.

SSIS

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.

SQL Job StepSQL Job Schedule

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. .