1172
Part IX: Business Intelligence
Connection properties to specify which database the query runs against.
SQLSourceType specifi es how to enter the query:■ (^) Direct Input: Enter into the SQLStatement property by typing in the property
page, pressing the ellipses to enter the query in a text box, pressing the Browse
button to read the query from a fi le into the property, or pressing the Build
Query button to invoke the Query Builder.
■ (^) File connection: Specify a fi le that the query will be read from at run time.
■ Variable: Specify a variable that contains the query to be run.
A query can be made dynamic by using parameters. Parameter use is limited — only in the
WHERE clause and, with the exception of ADO.NET connections, only for stored procedure
executions or simple queries. If parameters are to be used, then the query is entered with a
marker for each parameter to be replaced, and then each marker is mapped to a variable via
the Parameter Mapping page. Parameter markers and mapping vary according to connection
manager type:
■ (^) OLE DB: Write the query leaving a? to mark each parameter location, and then
refer to each parameter using its order of appearance in the query to determine a
name: 0 for the fi rst parameter, 1 for the second, and so on.
■ ODBC: Same as OLE DB, except parameters are named starting at 1 instead of 0.
■ (^) ADO: Write the query using? to mark each parameter location, and specify any
non-numeric parameter name for each parameter. For ADO, it is the order in which
the variables appear on the mapping page (and not the name) that determines
which parameter they replace.
■ (^) ADO.NET: Write the query as if the parameters were variables declared in Transact-
SQL (for example, SELECT name FROM mytable WHERE id = @ID), and then
refer to the parameter by name for mapping.
The ResultSet property (General page) specifi es how query results are returned to
variables:
■ None: Results are not captured.
■ (^) Single row: Results from a singleton query can be stored directly into variables. On
the Result Set tab, map each result name returned by the query to the correspond-
ing target variable. As with input parameters, result names vary according to con-
nection manager type.
■ (^) Full result set: Multiple-row result sets are stored in a variable of type Object for
later use with a Foreach loop container or other processing. On the Result Set tab,
map a single result name of 0 (zero) to the object variable, with a result type of Full
Result Set.
■ (^) XML: Results are stored in an XML DOM document for later use with a Foreach loop
container or other processing. On the Result Set tab, map a single result name of 0
(zero) to the object variable, with a result type of Full Result Set.
c52.indd 1172c52.indd 1172 7/31/2012 10:29:28 AM7/31/2012 10:29:28 AM
http://www.it-ebooks.info