Dynamic WHERE Conditions

The following article shows how to define dynamic WHERE conditions for Table extractions.

The dynamization of the Xtract Table components increases the maintainability of SSIS packages. The depicted scenario uses SSIS expressions based on table properties for this functionality.

How to use [Xtract Table].[WhereClause]

To create a dynamic WHERE condition using the Xtract Table property [Xtract Table].[WhereClause]:

1. Create an SSIS package and add an Xtract Table component.
2. Look up the table you want to extract. The depicted example uses the SAP standard table BKPF - Accounting Document Header and the table field BUDAT - Posting Date in the Document.
3. Right-click on the Data Flow canvas and open Properties.
   
4. Add an SSIS expression using the Expressions property. Click [...] next to the Expressions property. The window "Property Expressions Editor" opens.
5. Select the property [Xtract Table].[WhereClause].
   
6. Open the Expression Builder by clicking [...].

7. Enter an expression to create a dynamic WHERE clause. In the depicted example, only results until 01.01 of the current fiscal year are extracted:

   "BUDAT <= " + "'" +  (DT_STR, 4, 1252) DATEPART("yy" , GETDATE())  + "0101'"
   

   

8. Click [Evaluate Expression] to check if the syntax is correct.

9. Click [OK] in the Expression Builder and the Property Expression Editor to confirm the input.
10. Check the WHERE-Clause of the table extraction in tab WHERE Clause.
    
11. Run the SSIS package.

Dynamic WHERE conditions are successfully applied to filter table extraction data.

SSIS Expression Samples

"BUDAT >= " + "'" + (DT_STR, 4, 1252) DATEPART( "yy", DATEADD( "yy", -1, GETDATE() ) ) + "%'"