SQL Server Integration Services

Example 5: Variables

This example uses a user-defined variable in the WHERE clause of a T-SQL SELECT statement to limit the result set to a single sales person.

1. Create a copy of Example1.dtsx following the instructions at the beginning of Example 2: Aggregation, and rename it Example5.dtsx.

2. Open the Example5 package and switch to the Data Flow designer.

3. The DataReader Source does not support parameterized queries, but the OLE DB Source does. Delete the Sales Order Source from the designer.

4. Drag an OLE DB Source onto the designer above the Sales Order Destination item. Rename the OLE DB Source to Sales Order Source.

5. Double-click Sales Order Source to open the OLE DB Source Editor dialog. Create an OLE DB Connection Manager by clicking the New... button next to the OLE DB Connection Manager dropdown. This opens the Configure OLE DB Connection Manager dialog. Select the localhost.AdventureWorks1 OLE DB connection manager (created in Example 3: Lookup), in the "Data connections" list, and press the OK button to close the dialog.

6. Select SQL Command from the "Data access mode" dropdown. Enter the following text into the SQL command text field, or click the Build Query... button the open the Query Builder dialog letting you graphically create the same:

   SELECT Sales.SalesOrderHeader.*
   FROM   Sales.SalesOrderHeader
   WHERE  (SalesPersonID = ?)

   Notice that the query is parameterized for the SalesPersonID column.

7. Click the Parameters... button next to the "SQL command" text field to open the Set Query Parameters ...