Execute Process Package Sample

  • Article

The Execute Process sample is a package that runs an executable from within the package. In this case, the sample package includes an Execute Process task that runs the Expand utility (expand.exe). The Expand utility decompresses a CAB file that contains an Excel spreadsheet subsequently used as source data for the rest of the package. The Execute_Process_Table table is created by an Execute SQL task the first time the package runs. The Transact-SQL statement that creates the table is stored in a separate file. The Execute SQL task truncates Execute_Process_Table when the package is run again.

An OLE DB source reads the data from the Excel spreadsheet and an OLE DB destination writes the data to Execute_Process_Table.

If you run the sample on a non-English version of Windows, you may have to substitute the localized name of the Program Files folder to open or run the sample.

Important

Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.

Requirements

Running this sample package requires the following:

  • The sample package and data files that it uses must be installed on the local hard disk drive.
  • You must have installed and have administrative permissions on the AdventureWorks database.
  • If you intend only to run the sample package from the command line, you must install SQL Server 2005 Integration Services (SSIS).
  • If you intend to open the package in SSIS Designer and run the sample package, you must install Business Intelligence Development Studio.
  • You may have to modify the Execute Process Task to provide the correct path of the Expand utility. Depending on the operating system, the Expand utility may be located in a different directory than that specified in the sample package. To modify the Execute Process task, right-click the task in the Control Flow tab and select Edit. In the Execute Process Task Editor, set the path of the Executable property to the folder where the Expand utility is located.

For more information about how to install samples, see the topic, "Installing Sample Integration Services Packages", in SQL Server 2005 Books Online. To obtain the latest version of the samples, including new samples released after the original release of SQL Server 2005, see SQL Server 2005 Samples and Sample Databases (April 2006).

Location of the Sample Package

If the samples were installed to the default installation location, the Execute Process sample package is located in the following folder:

C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\ExecuteProcess Sample\ExecuteProcess\

The following files are required to run this sample package.

File Description

UsingExecuteProcess.dtsx

The sample package.

Customers.cab

The CAB file that contains the compressed Excel spreadsheet used as the data source in the package.

Create_Execute_Process_Dest.sql

A Transact-SQL query that creates a destination table in the AdventureWorks database.

Running the Sample

The package can be run from the command line by using the dtexec utility, or can be run in Business Intelligence Development Studio.

If you are using a non-English version of Windows, you may have to update the ConnectionString property of any file connection managers used in the package to run the sample package successfully. You should verify that the path used in the connection manager is valid on your computer, and if you need to, modify the path so that it uses the localized name of the Program Files folder.

Important

This sample uses the Microsoft Jet 4.0 OLE DB provider, for which there is no 64-bit version. The package fails on a 64-bit computer.

For this sample, you may have to update "Program Files" in the ConnectionString property for the orders.xml, TransformToFF, and XsltResult connection managers.

To run the package by using dtexec

  1. Open a Command Prompt window.

  2. Change the directory to C: Program Files\Microsoft SQL Server\90\DTS\Binn, the location of dtexec.

  3. Type the following command:

    dtexec /f "C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\ExecuteProcess Sample\ExecuteProcess\UsingExecuteProcess.dtsx"

  4. Press Enter.

For more information about how to run the package by using the dtexec utility, see the topic, "dtexec Utility", in SQL Server 2005 Books Online.

To run the package in Business Intelligence Development Studio

  1. Open Business Intelligence Development Studio.

  2. On the File menu, point to Open, and then click Project/Solution.

  3. Locate the ExecuteProcess Sample folder, and then double-click the file named ExecuteProcess.sln.

  4. In Solution Explorer, right-click ExecuteProcess.dtsx in the SSIS Packages folder, and then click Execute Package.

For more information about how to run the package in Business Intelligence Development Studio, see the topic, "Running Packages", in SQL Server 2005 Books Online.

Components in Sample

The following table lists the tasks, sources, and destinations that are used in the sample.

Element Purpose

Execute SQL task

Executes the Transact-SQL query in the file Create_Execute_Process_Dest.sql to create the destination table in the Adventure Works database. The table is created the first time that you run the package. If you run the package, the table is truncated.

Execute Process task

Runs the Expand utility, which decompresses Customers.cab.

Data Flow task

Runs the data flow in the package.

OLE DB source

Uses the decompressed Excel spreadsheet as the data source.

OLE DB destination

Saves the data in the Excel spreadsheet to the destination table that was created by the Execute SQL task.

File connection manager

Connects to the file that contains the SQL statement that creates the Execute_Process_Table table.

OLE DB connection managers

One OLE DB connection manager connects to the Excel spreadsheet by using the Microsoft Jet 4.0 OLE DB provider; the other connects to the AdventureWorks database by using the SQL Native Client.

Sample Results

To see the execution results of the Execute Process sample package, open SQL Server Management Studio. In Object Explorer, connect to the server that contains AdventureWorks. In SQL Server Management Studio, click New Query and select Database Engine Query. In the database list, which defaults to the master database, select the AdventureWorks database, or type USE AdventureWorks in the query window and press F5 to change the database.

Type and run the following Transact-SQL query:

Select * from AdventureWorks.dbo.Execute_Process_Dest