Exploring Package Execution Options

Packages can be run in various wizards and applications; however, packages are most often run in SQL Server Business Intelligence Development Studio (BIDS) during the development, debugging, and testing of packages. The Execute Package task extends the enterprise capabilities of SSIS by enabling packages to run other packages as part of a workflow. A package that runs another package is generally referred to as a parent package, and the packages that a parent workflow runs are called child packages. This section discusses the various methods for creating and executing packages.

Using the SQL Server Import and Export Wizard to Execute Packages

The SQL Server Import and Export Wizard provides the simplest method for creating and executing a simple package. You can start the SQL Server Import and Export Wizard from an Integrations Services package in BIDS, or you can start the wizard from SQL Server Management Studio; however, you can execute a package only if you start the SQL Server Import and Export Wizard in SQL Server Management Studio.

Start the SQL Server Wizard from BIDS

1. In BIDS, create an SSIS project by performing the following steps:

   1. On the File menu, click New, and then click New Project.

   2. In the New Project dialog box, provide a name, location, and solution for your project, and then click OK.

2. In Solution Explorer, right-click the SSIS packages folder, and then click SSIS Import And Export Wizard. The SQL Server Import And Export Wizard appears.

3. Follow the wizard steps, and then save the package in your project.

   Note 

   A package cannot be executed if you start the SQL Server Import And Export Wizard from an Integrations Services package in BIDS.

Start the SQL Server Wizard from Management Studio

1. In Management Studio, connect to a database engine by performing the following steps:

   1. On the File menu, click Connect Object Explorer. The Connect to Server dialog box appears.

   2. Click Connect.

2. In Object Explorer, expand the Databases folder.

3. Right-click the name of the database you created earlier (configdb), click Tasks, and then click either Import Data or Export Data. The SQL Server Import And Export Wizard appears.

4. Follow the wizard steps, save the package, and then decide whether to execute the package.

Using DTExecUI to Execute Packages

Another effective tool for executing packages is the SSIS package execution utility DTExecUI. DTExecUI is a command prompt utility that enables you to run packages from a command prompt or from within SQL Server Management Studio.

Run a Package by Using the DTExecUI Utility

1. To open a command prompt window, click Start, and then click Run. The Run box appears.

2. In the command prompt window, type dtexecui, and then press Enter. The Execute Package Utility dialog box opens.

3. In the Execute Package Utility dialog box, specify any of the following:

   • Package to execute

   • XML configuration files

   • Connection Manager connection strings

   • Variables

   • Other package properties

4. Click Execute to run the package. Wait until the package has executed successfully.

Using DTExec to Execute Packages

The DTExec utility provides access to all the package configuration and execution features, such as connections, properties, variables, logging, and progress indicators. It enables you to load packages from three sources: a SQL Server database, the SSIS service, and the Windows file system.

The DTExec utility proceeds through four phases as it executes:

• Command sourcing phase

• Package load phase

• Configuration phase

• Validation and execution phase

DTExec enables you to run packages from a command prompt. The basic syntax is dtexec /option [value] [/option [value]]. The Execute Package Utility contains a command-line window. This window contains the /option [value] part of the dtexec syntax. The dtexec command can be saved in a *.bat or *.cmd file, and then the package can be run by executing one of these files. This is particularly useful with enterprise job-scheduling software. The SSIS developers can create the *.cmd files, and then the job-scheduling software needs only to execute the *.cmd files.

On a 64-bit server, the 64-bit version of dtexec.exe is located at \Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe. The 32-bit version of dtexec.exe is located at \Program Files\Microsoft SQL Server (x86)\90\DTS\Binn\DTExec.exe. It is sometimes useful to execute SSIS in 32-bit mode on a 64-bit server. For example, there is no 64-bit Microsoft Access OLE DB (Jet engine) driver.

More Info 

For more information about the DTExec utility, see the SQL Server 2005 Books Online article titled "dtexec Utility" at http://msdn2.microsoft.com/en-us/library/ms162810.aspx.

Run a Package by Using the DTExec Utility

1. Open a command prompt window.

2. In the command prompt window, type dtexec / followed by the DTS, SQL, or File option and the package path, including package name.

3. If the package encryption level is EncryptSensitiveWithPassword or EncryptAllWithPassword, use the Decrypt option to provide the password. If no password is included, dtexec will prompt you for the password.

4. Optionally, provide additional command-line options.

5. Press Enter.

6. Optionally, view logging and reporting information before closing the Command Prompt window. The Execute Package Utility dialog box opens.

7. In the Execute Package Utility dialog box, click Execute Package. Wait until the package has executed successfully.

Using SQL Server Management Studio to Execute a Package

In SQL Server Management Studio, in Object Explorer, you can connect to an SSIS server, expand the Stored Packages folder, and then expand either the File System or MSDB folder.

Run a Package by Using SQL Server Management Studio

1. In SQL Server Management Studio, right-click a package, and then click Run Package.

   Execute Package Utility opens.

2. Execute the package as described previously.

Extending Package Execution Options

You can extend the enterprise capabilities of SSIS by using the Execute Package and Execute Process tasks.

Execute Package Tasks

The Execute Package task enables packages to run other packages as part of a workflow. You can use the Execute Package task for the following general purposes:

• Breaking down complex package workflow

• Reusing parts of packages

• Grouping work units

• Controlling package security

For example, you can use the Execute Package task to modularize your packages. Each child package can perform a small, well-defined unit of work, and then a parent package can contain multiple Execute Package tasks to execute all of the child packages. One common use is to have one package per data warehouse dimension and then have a parent package that executes all of the dimension packages.

A child package can be executed by a parent package, using an Execute Package task. The child package can use a configuration to read the parent package variables.

Execute Process Tasks

The Execute Process task can execute any file that can be executed from the command prompt. This includes the SSIS Command Line Utility (DTExec). You can specify the executable and command-line arguments. You can also execute a *.bat or *.cmd file that contains a dtexec command.

Using SQL Server Agent

SQL Server Agent is a Windows service that executes scheduled administration tasks, which are called jobs. It uses SQL Server to store job information. Jobs contain one or more job steps. Each step contains its own task, for example, backing up a database. SQL Server Agent can run a job on a schedule, in response to a specific event, or on demand.

SQL Server Agent can be configured to execute an SSIS package. It can also be configured to modify the package XML configuration files, connection manager strings, variables, and other package properties.

Execute Tasks and Containers and then Disable the Task and Execute the Package

1. In BIDS, in Solution Explorer, double-click  NewProducts.dtsx to open the package designer.

2. On the Control Flow tab, right-click Execute SQL Task - Clear Product Names, and then click Execute Task.

3. In SQL Server Management Studio, in the QuickStartODS database, click the Table - dbo.ProductNames tab, and then, on the Query Designer toolbar, click the exclamation mark (!) (Execute SQL).

   Verify that the QuickStartODS database ProductNames table is empty.

4. In BIDS, on the Debug menu, click Stop Debugging.

5. Right-click Sequence Container - File Doesn't Exist, and then click Execute Container.

6. In SQL Server Management Studio, click the Table - dbo.ProductNames tab, and then click the exclamation mark (!) (Execute SQL).

   Verify that the QuickStartODS database ProductNames table now contains data.

7. In BIDS, on the Debug menu, click Stop Debugging.

8. On the Control Flow tab, right-click Foreach Loop Container - FuzzyInput Files, and then click Disable.

9. In Solution Explorer, right-click  NewProducts.dtsx, and then click Execute Package. Wait until the package has executed successfully. Your screen should resemble this:

   

10. In SQL Server Management Studio, in Object Explorer, expand QuickStartODS, expand the Tables folder, right-click dbo.MatchedNames, and then click Open Table.

    Notice that the tab in the middle of the screen changes to read dbo.MatchedNames.

11. Click the Table - dbo.MatchedNames tab, and then click the exclamation mark (!) (Execute SQL).

12. On the Table - dbo.MatchedNames tab, verify that the table is empty.

13. In BIDS, on the Debug menu, click Stop Debugging.

14. On the File menu, click Save All.

15. Close the  NewProducts.dtsx package.

Using the Execute Package Utility

In this procedure, you'll learn how to use the Execute Package Utility.

Use the Execute Package Utility

1. In Windows Explorer, navigate to C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap08\XPkgUNewProducts.dtsConfig.

2. Right-click  XPkgUNewProducts.dtsConfig, click Open With, and then click Microsoft Visual Studio Version Selector.

   In BIDS, on the  XPkgUNewProducts.dtsConfig tab, between the <ConfiguredValue> tags, notice that the name of the destination file has been changed to  XPkgUProductsDestination.txt.

   

3. In Windows Explorer, double-click the C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap08\NewProducts.dtsx file. Execute Package Utility opens.

4. In Execute Package Utility, in the left pane, click Configurations, and then click Add. The Open dialog box appears.

5. In the Open dialog box, browse to C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap08\XPkgUNewProducts.dtsConfig, and then click Open.

6. In the Execute Package Utility dialog box, in the left pane, click each option to see the changes that can be made to a package when it is executed using Execute Package Utility.

7. Click Execute. The Package Execution Progress dialog box opens. This dialog box shows event log entries as the package executes.

   

8. When package execution completes, click Close to close the Package Execution Progress dialog box.

9. Leave the Execute Package Utility dialog box open.

10. In Windows Explorer, verify that the NewProducts package created the C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap08\Data\XPkgUProductsDestination.txt file.

Use the Command-Line Utility

1. In the Execute Package Utility dialog box, in the left pane, click Command Line.

2. Copy all of the text in the Command Line box.

3. In Windows Explorer, right-click C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap08\ExecuteNewProducts.cmd, and then click Edit.

4. In  ExecuteNewProducts.cmd - Notepad, replace <paste here> with the text you copied from the Command Line box.

5. On the File menu, click Save.

6. Close the file, and then close Execute Package Utility.

7. In Windows Explorer, double-click C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap08\ExecuteNewProducts.cmd.

   The command-line utility executes the NewProducts package.

8. When package execution completes, press any key to close the command-line window.

Executing a Package by Using SQL Agent

1. In SQL Server Management Studio, verify that you are connected to the SQL Server database engine. (If you are not connected, on the File menu, click Connect Object Explorer, and then, in the Connect to Server box, click Connect.)

2. In the left pane, toward the bottom of the tree, right-click the SQL Server Agent folder, and then click Start.

   Note 

   By default, the SQL Server Agent service is stopped. To start the service, in Object Explorer, right-click the SQL Server Agent folder, and then click Start.

3. Right-click the Jobs folder (located under the SQL Server Agent folder), and then click New Job. The New Job dialog box opens.

4. In the New Job dialog box, in the left pane, click General, and then, in the Name text box, type ExecutePackageJob.

   

5. In the left pane, click Steps, and then click New. The New Job Step dialog box opens.

6. In the New Job Step dialog box, in the Step Name text box, type ExecutePackageStep.

7. In the Type drop-down list, select SQL Server Integration Services Package.

8. In the Package Source drop-down list, select File System.

9. In the Package text box, navigate to C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap08\NewProducts.dtsx, and then click Open.

10. Click OK to close the New Job Step dialog box, and then click OK to close the New Job dialog box.

11. In SQL Server Management Studio, in the left pane, expand the Jobs folder, right-click ExecutePackageJob, and then click Start Job At Step. SQL Server Agent executes the NewProducts package.

12. After the package executes, click Close to close the Start Jobs dialog box.

    Now that the SQL Server Agent job is created, the package can be executed on a periodic basis by using the scheduling features in SQL Server Agent.