Readme_Execute SQL Parameters and Result Sets Sample

  • Article

[This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]

This sample works only with Microsoft SQL Server 2008. This sample will not work with any version of SQL Server earlier than SQL Server 2008.

This sample works with the SQL Server 2005 version of the AdventureWorks OLTP database. To install this database, see Sample Databases for Microsoft SQL Server 2008.

The Execute SQL Parameters and Result Sets sample is a package that uses a parameterized stored procedure and an SQL statement to query the AdventureWorks database. The package stores the query results in package variables by mapping parameters and result sets to the variables.

Note

This sample package demonstrates how to configure the Execute SQL task to use parameters and result sets. However, this is not the typical approach to querying related tables and returning data. A best practice for such a query is to use a Lookup transformation that connects to a source component and a destination component.

To query the related tables and return data, this sample package uses the following Integration Services components:

  • An Execute SQL task that creates a stored procedure that runs later in the package.
  • A second Execute SQL task that queries a view in the AdventureWorks database for employee last names, and then stores the table rows in a variable of the Object data type.
  • A Foreach Loop container that extracts the table row values, which are stored in the variable of the Object data type, into separate variables. To extract and separate values, the Foreach Loop container uses these Integration Services components:
    • An Execute SQL task that executes the stored procedure, which uses input and output parameters mapped to package variables, to query the AdventureWorks database for the sales total for each employee.
    • A script task that writes the sales totals and employee last names to a file.

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

To run this sample package, the following components are required:

  • The sample package and data files that the package uses must be installed on the local hard disk drive.
  • You must have installed and have administrative permissions on the AdventureWorks OLTP database.
  • If you run the sample package only from the command line, you must install Integration Services.
  • If you open the package in SSIS Designer and run the sample package, you must install Business Intelligence Development Studio.

For more information about how to install samples, see "Considerations for Installing SQL Server Samples and Sample Databases" in SQL Server Books Online.

Location of the Sample Package

If you installed the samples to the default installation location, the Execute SQL Parameters and Result Sets sample is located in the following folder:

C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\ ExecuteSQLParametersResultSets Sample\ExecuteSQLParametersResultSets

To run this sample package, the files listed in the following table must be stored on the computer that is running the package.

File Description

ExecuteSQLParametersResultsSets.dtsx

The sample package.

GetEmployeeSalesYTD.sql

The stored procedure that returns employee last names from a view in the AdventureWorks database.

CheckQueryResults.txt

The text file that contains the results of the query that the connected Execute SQL task runs.

Running the Sample

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

If you are using a non-English version of Windows, you might have to update the ConnectionString property of any file connection managers that the package uses. Otherwise, the sample package might not run successfully. Before running the package, do the following:

  • Verify that the path in the ConnectionString property of any file connection managers is valid on your computer. This sample includes the Check Query Results and GetEmployeeSalesYTD.sql connection managers.
  • If the path is not valid, modify the path so that the path uses the localized name of the Program Files folder..

Running the Sample at the Command Prompt

Use the following procedure to use the dtexec utility to run this sample package. For more information about how use the dtexec utility to runs a package, see the topic, "dtexec Utility," in SQL Server Books Online.

To run the package by using dtexec

  1. Open a Command Prompt window.

  2. At the command prompt, change the directory to <drive>:\Program Files\Microsoft SQL Server\100\DTS\Binn.

    This directory contains the dtexec utility.

  3. Type the following command:

    dtexec /f "C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\ ExecuteSQLParametersResultSets Sample\ExecuteSQLParametersResultSets \ExecuteSQLParametersResultSets.dtsx "

  4. Press ENTER.

Running the Sample at the Command Prompt

Use the following procedure to use Business Intelligence Development Studio to run this sample package.

To run the package in Business Intelligence Development Studio

  1. Open Business Intelligence Development Studio.

  2. Create a new project and a new Integration Services solution.

    Business Intelligence Development Studio adds the new solution to Solution Explorer and the new project to the solution. For more information, see How to: Create a New Integration Services Project.

  3. In Solution Explorer, right-click the SSIS Packages folder, and then click Add Existing Package.

  4. Add the ExecuteSQLParametersResultSets.dtsx package by doing the following steps:

    1. In the Package location list, select File System.
    2. Click the browse button (...), select the ExecuteSQLParametersResultSets.dtsx package, and then click OK.

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

Components in the Sample

The following table lists the Integration Services tasks and connection managers that the sample uses.

Element Purpose

Execute SQL task

The Execute SQL task, Create Stored Procedure, creates a stored procedure that the Execute SQL task, Query for Year-to-Date Sales Totals, runs.

Execute SQL task

The Execute SQL task, Query for Employee Last Names, runs an SQL statement that queries the vEmployee view in the AdventureWorks database. The task stores the results as a Full result set in a package variable of Object data type, SQLQueryResults_Name.

The SQL statement returns records with the LastName column value that is based on the value of the EmployeeID column.

Foreach Loop container

The Foreach Loop container, Enumerate Rows Containing Last Name, iterates through each table row that is stored in the SQLQueryResults_Name variable and extracts the column value into a package variable. The variable is mapped to the column. The Foreach Loop container uses the Foreach ADO Enumerator to enumerate the table rows.

Execute SQL task

The Execute SQL task, Query for Year-to-Date Sales Totals, uses an SQL statement to run the stored procedure that returns employee last names from a view in the AdventureWorks database.

The SQL statement includes parameters mapped to package variables. The input parameter passes the LastName variable value to the stored procedure. The output parameter stores the sales value, which the stored procedure returns, in the SalesYTD variable.

This Execute SQL task is in the Foreach Loop container, Enumerate Rows Containing Last Name.

Script task

The Script task, Write to File Employee Names and Year-to-Date Sales Totals, writes the sales totals and employee last names that are stored in package variables to a file.

This Script task is in the Foreach Loop container, Enumerate Rows Containing Last Name.

Flat File connection manager

The Flat File connection manager, Check Query Results, connections to the file to which the Script task writes.

File connection manager

The File connection manager, GetEmployeeSalesYTD.sql, connects to the file that contains the stored procedure.