Business Intelligence Development Studio is well-suited to creating and testing SSIS packages, but batch files using the DTExec command are a more convenient means of executing production SISS packages.

The DTExec.exe command requires one parameter, the path to a valid SSIS package. The path to a configuration file can be added to override parameters hard-coded into the package, along with other switches to select execution options such as error output mode and log file path.

In this article we will:

1. Determine the path to the desired version of DTExec.exe
2. Configure the connections of an SSIS package with SQL Server authentication
3. Execute the package from the command line
4. Execute the package from a batch file
5. Create a configuration file for the SSIS package
6. Modify the configuration file to change the server name and the user name and add the password
7. Add the configuration file path to the batch file
8. Execute the package from the modified batch file

1. Determine the path to the desired version of DTExec.exe

Search your hard drive to determine how many versions of DTExec.exe are installed on your computer.

In the Agent Ransack search above there are 32- and 64-bit versions of DTExec.exe for SQL Server 2008 and 2012.

We are going to execute 64-bit SQL Server 2012 SSIS packages, so we will use the DTExec.exe path "C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe."

2. Configure the connections of an SSIS package with SQL Server authentication

The most straightforward method of executing an SSIS package from the command line or a batch file with DTExec.exe is to save the package with the production connection information embedded in each connection, as shown below.

After the connections are configured, save and run the SSIS package in BIDS to confirm that it successfully executes. 

(In case you are wondering, passwords are stored in the SSIS package file in encrypted form.)

3. Execute the package from the command line

Save the SSIS package to a convenient location and make note of the path, in this case "C:\SSIS\TestPackage.dtsx."

Open a command console and enter the command:

"c:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTEXEC.exe" /File "C:\SSIS\TestPackage.dtsx"

Press Enter to execute the package.

The DTSER_SUCCESS (0) message indicates that the SSIS package executed successfully.

If it had failed, the message would read DTSER_SUCCESS (-1)

4. Execute the package from a batch file

Paste the following SQL code...

@ECHO OFF
CLS
ECHO You are about to execute the TestPackage SSIS package
PAUSE
"c:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTEXEC.exe" /File "C:\SSIS\TestPackage.dtsx"
PAUSE

...into a text editor and save the file as TestBatchFile.bat.

Make sure you have the extension ".bat" and place this in a convenient location.

Double-click the new batch file to execute it.

The DTSER_SUCCESS (0) message indicates that the package executed successfully.

5. Create a configuration file for the SSIS package

Create a new Integration Services Project in Business Intelligence Development Studio named PackageDeploymentProject.

Open the previous SSIS package in BIDS.

Convert the project to a Package Deployment Model project.Right click in Solution Explorer to get the menu item.

Click the OK button.

Click the OK button again.

Select Logging - Package Configurations from the top menu.

Check the Enable package configurations checkbox and click the Add button.

Click the Browse button.

Enter the configuration file name TestPackage.dtsConfig and click the Save button.

Click the Next button.

Check the ConnectionString property for each connection manager, then click the Next button.

Change the configuration name to TestPackage_Configuration and click the Finish button.

Click the Close button.

Select Build - Build PackageDeploymentProject (package deployment model) from the top menu.

Browse to the new configuration file in the PackageDeploymentProject project and copy it to..

...a convenient location.

6. Modify the configuration file to change the server name and user name and add the password

Open the configuration file in XML Notepad or some other editor.

Modify the Data Source and User ID parameters as necessary, and add the ";Password=XXXX" parameter.

7. Add the configuration file path to the batch file

Modify the batch file to add the configuration file path to the batch command.

8. Execute the package from the modified batch file

Double-click the modified batch file to execute the SSIS package with the new connection manager parameters