Effective Big Data ETL with SSIS, Pig, and Sqoop - Microsoft Big Data Solutions - page 195

Making the Query Dynamic
You may have noticed that neither the ADO.NET Source nor the ODBC
Source offers the ability to parameterize the queries. In many cases, you
will need the WHERE clause to be dynamic at run time. To achieve this, you
can use expressions on the SQLCommand properties exposed by the source
components. An SSIS expression enables you to create a formula that is
evaluated at run time.
One common scenario with Hive that requires the use of expressions is
running a query that filters the results by a specified time period. For an
example of this, do the following:
1. Add two DateTime variables to your package, and configure them with
an appropriate date range.
2. Select the background of the Data Flow Task containing your Hive
source component.
3. In the Properties window, select the Expressions property and click the
ellipsis button to bring up the Property Expression dialog.
4. Create a new entry for the property named
[NameOfYourSource].[SqlCommand].
5. In the expression, enter the following:
"SELECT * FROM Customer WHERE LastSaleDate BETWEEN
\"" +
(DT_WSTR, 50) @[User::FromDate] + "\" AND \"" +
(DT_WSTR, 50)
@[User::ToDate] + "\""
This creates an expression that will set the SqlCommand property
dynamically at run time using the values stored in the FromDate and
ToDate variables. If you take this approach, the resulting SSIS package is
more flexible and can incorporate dynamic filtering of the Hive source.