How to: Map Query Parameters to Variables in Data Flow Components

To map query parameters to variables in the data flow, you have to use an OLE DB source. The OLE DB Source uses parameterized queries to filter data.

To map a query parameter to a variable

In Business Intelligence Development Studio, open the Integration Services project that contains the package you want.
In Solution Explorer, double-click the package to open it.
Click the Data Flow tab, and then, from the Toolbox, drag the OLE DB source to the design surface.
Right-click the component and click Edit.
Select an OLE DB connection manager to use to connect to the data source, or click New to create a new OLE DB connection manager.
In the OLE DB Source Editor, for Data access mode, select SQL command, and then in SQL command text, type a parameterized query.
Click Parameters.
In the Set Query Parameters dialog box, map each parameter in the Parameters list to a variable in the Variables list, or create a new variable by clicking <New variable>. Click OK.

Note

Only system variables and user-defined variables that are in the scope of the package, a parent container such as a Foreach Loop, or the Data Flow task that contains the data flow component are available for mapping. The variable must have a data type that is compatible with the column in the WHERE clause to which the parameter is assigned.

You can click Preview to view up to 200 rows of the data that the query returns.
To save the updated package, click Save Selected Items on the File menu.