By Ken Whiteside

DataXperts

Using Variables in Expressions.

Occasionally you will want to manipulate your data sources with ranges that are derived from the current logic flow. This can be done by forcing an expression to replace the datasource at runtime. This isn’t the easiest feature to use because it is so buried and very difficult to debug.

Over-riding the Data Source

Let’s say you have a Data flow task where you read some data. The range of the data is unknown until runtime. Click on the Dataflow task and notice the Properties has an Expressions object, and that it is collapsed.

Expand the Expressions object to see that there can be expressions there.

Click the ellipsis to set your expressions. In this case we are using a DataReader that points to an Oracle table. We want to make of data we retrieve variable. Select the property for the DataReader Source as shown below, and then click the Ellipsis in the expression window. This will take you to the Expression Builder as shown below.

There are many nuances with the expression builder and not a lot of debugging help. The best suggestion I can give is to build it up step by step to make sure you have the syntax on the right track. For example, start out with the query in quotes without the variables, and test it to see if it works. That is just put the date ranges in there. Once you get that working, move on to the variables. Oracle makes it even trickier in that the Dates are formatted differently and need the DT_WSTR conversion applied. If you have Toad or SQL_plus available, you should validate your formatting in those environments. This took me quite a while I must admit and only go here as last resort as it is always time consuming to get it right.

My experience has led be to believe that Microsoft and Oracle rarely play well together. If you cross those bridges, each company seems to make it seem difficult to encourage you to stick with what you know.

Good thing Google exists!