CODEWITHSUNDEEP
ssisdb2
mehtat_90
mehtat_90

Reputation: 628

Expression Builder SSIS

I am trying to write the query dynamically in SSIS Expresion Builder but I am stuck with an error and would really appreciate all your help. Here my source is DB2

My query is :-

Select * FROM schema.table_name
WHERE column_a < 100
OR (column_a >= 100 AND column_b = @[User::days]
FOR FETCH ONLY WITH UR

Note:@[User::days]= current date - x days

Upvotes: 0

Views: 115

Answers (1)

billinkc
billinkc

Reputation: 61221

That's not how it works.

You can either use an SSIS Variable with an Expression to satisfy this requirement or, assuming your source supports it, parameterize the query.

Expression

Add a Variable to the SSIS package. Call it QuerySource, type is String.

If 2012+, in the Expression, not Value, use the following formula

"Select * FROM schema.table_name WHERE column_a < 100 OR (column_a >= 100 AND column_b " 
+ DT_WSTR(5) @[User::days] 
+ " FOR FETCH ONLY WITH UR"

If 2005/2008, you will then need to right click on the row in the Variables window and select Properties. In the resulting window, you will need to set EvaluateAsExpression to True as well as copy the above into the Expression property.

The carriage returns above are for readability. They may or may paste well into your version of BIDS/SSDT

Now that you've created your Variable, you'll need to use it in the source. Assuming OLEDB, you will want to select Data Access Mode of "SQL Command from Variable". If you're using an ADO.NET source, then you'll need to to the Control Flow, single click the Data Flow Task and right click and select Properties. From the Properties window, find Expressions. Click the ellipses. Select the the ADO.NET source. Assign the Variable as the Source.

Parameterization

Certain sources, like OLEDB, support parameterization. Set your Data Access Mode to SQL Command.

Select * FROM schema.table_name
WHERE column_a < 100
OR (column_a >= 100 AND column_b = ?
FOR FETCH ONLY WITH UR

The ? is an ordinal based replacement character for OLE DB connections. Click the Parameters button and assign your @[User::days] variable to it.

Upvotes: 1

Related Questions