DB2 Source Patterns - SQL Server Integration Services Design Patterns - page 177

Be sure to click the Parameters button next to the query in the source to assign vari-
ables to each parameter that you set. It is important that the list of variables in the Para-
meters window match the correct order in the query.
There are some scenarios where using query parameters won't work. Let's look at
when you can't use query parameters and what to do instead.
DB2 Source Component Dynamic Queries
Parameterized queries will not work if the content of the source query needs to be
changed for any reason. The table, schema, or column names could change as part of
the query content. A typical example of this in DB2 is when you have different schem-
as in each environment. To fix this, we set an expression on a variable and use the vari-
able in the SQLStatement property. Let's walk through the steps of setting this up.
Begin by creating two string variables: environment and query . Set the fol-
lowing properties on the query variable:
EvaluateAsExpression: True
Value: "select col1, col2 from" + @environment + ".tab1"
Note In Integration Services's editions prior to SQL Server 2012, expressions had a
limit of 4000 characters. This restriction is now removed, allowing you to create strings
as long as you need them to be.
In the OLE DB source component, change the query type to SQL Command from
Variable, and pick the query variable that you just selected, as shown in Figure 6-8 .