SSIS getting started tutorial (1)-Execute Process Package example [translation] Level 100

Location: Samples and Sample Databases> Samples> SQL Server Integration
Services Samples> Package Samples>
Note: This article applies only to SQL Server 2005 Standard Edition and later versions, and does not apply to SQL Server 2005 Express

Execute
The Process package is an example of how to execute executable applications in the package. In this package, there is a task for executing the expandtool (expand.exe). The expand tool here will decompress a cab file containing an excel file.
In the next task, we can use this xls as the data source. When the package runs for the first time, an Execute SQL
Task, which automatically creates an Execute_Process_Table table and the T-SQL statement used to create the table is saved in an SQL file. When the second operation
When running an Execute SQL task, the system first checks whether the TABLE exists and runs TRUNCATE TABLE if it exists.

This task uses two OLEDB connections. The first OLEDB reads data from the Excel data table, and the second OLEDB writes the read data to the Execute_Process_Table table.

Configuration Requirements
To execute this example, you must meet the following requirements:

• The package and data file used in the example must be installed on the local hard disk.
• You must install the AdventureWorks database and have administrator-level permissions on the database.
• If you only want to run the sample package through the command line, you must install SQL Server 2005 Integration Services (SSIS)
• If you want to open and run the package in the designer, you must install Business Intelligence Development Studio
• You
  Execute may need to be modified.
  The path of the expand tool in the Process task, which is determined by the operating system you use. In different operating systems, expand.exe files are stored in different locations. Therefore, you can
  It can be different from the path in the example. To modify the Execute Process Task, right-click Execute on the Control Flow (Control Flow) tab.
  Process Task, click Edit. In the Execute Process Task
  In the process execution task Editor, set the path of the Executable attribute to expand's real storage path.

For more information about how to install the example, see installing the Integration Services sample package in SQL Server Books Online.

Storage location of the sample package
If the example is installed in the default directory, the Execute Process sample package is located in the following location:
C: \ Program Files \ Microsoft SQL Server \ 90 \ Samples \ Integration Services \ Package
Samples \ ExecuteProcess Sample \ ExecuteProcess \
The running sample package must contain the following files:

File	Description
UsingExecuteProcess. dtsx	Sample package
Customers. cab	The cab file that contains an Excel table. The Excel table will be used as a data source.
Create_Execute_Process_Dest. SQL	The query inAdventureWorksCreate a table in the database

Running example
This package can be run either in the command line using dtexec or in Business Intelligence Development Studio.

If you are using a non-English Windows system, you may need to update the connection
The ConnectionString attribute of all files used in the managers (Connection Manager). Only in this way can this package run properly. Check whether the path in the Connection Manager has
If necessary, change the path to the correct local path of Program Files.

Use dtexec to execute the package

1. Open the command line window (console)
2. Change the directory to C: Program Files \ Microsoft SQL Server \ 90 \ DTS \ Binn. This is the dtexec execution path.
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

Run the package in Business Intelligence Development Studio

1. Open Business Intelligence Development Studio
2. In the File menu, click Open> Project/Solution
3. Change the directory to the ExecuteProcess Sample directory and double-click ExecuteProcess. sln.
4. In Solution Explorer, right-click the ExecuteProcess. dtsx file in the SSIS Package directory, and click Execute Package

Objects in the example

Object	Function
Execute SQL task	Execute the Transact-SQL query contained in the Create_Execute_Process_Dest. SQL file.Adventure WorksCreate a target table in the database, but only create it when you run the package for the first time. When you run the package again, the table is cleared but not created.
Execute Process task	Run the Expand tool to decompress the Customers. cab file.
Data Flow task	Data Stream in the running package
Ole db source	Use an Excel data table as the source
Ole db destination	Save the data in the Excel table to the target table created by the Execute SQL task.
File connection manager	Connect to the SQL statement declaration file (. SQL), which is used to createExecute_Process_TableTable
Ole db connection manages	Contains two ole db connections. One ole db uses Microsoft Jet 4.0 ole db provider to connect to Excel, And the other uses SQL Native Client to connect to the AdventureWorks database.