Interesting question. Looking into it I found out that the data flow components don't support expressions, which would have been nice and the solution to your problem. And you can't modify a package within itself. So, to answer your question, its not possible.
So, in view of that, if your data flow is not very complex, you can try two copies of it, on with the cached lookup another without. You would use expressions on the precedence constraint to decide which one to execute during runtime.
|||Ravi G wrote:
Interesting question. Looking into it I found out that the data flow components don't support expressions, which would have been nice and the solution to your problem. And you can't modify a package within itself. So, to answer your question, its not possible.
So, in view of that, if your data flow is not very complex, you can try two copies of it, on with the cached lookup another without. You would use expressions on the precedence constraint to decide which one to execute during runtime.
Ravi,
That isn't necessarily true. Right-clicking on the background of the control flow and selecting properties will allow you to access the expressions box. In that expressions box, if a data flow component supports expressions, it will appear in that list.|||
Ravi G wrote: Interesting question. Looking into it I found out that the data flow components don't support expressions, which would have been nice and the solution to your problem. And you can't modify a package within itself. So, to answer your question, its not possible.
So, in view of that, if your data flow is not very complex, you can try two copies of it, on with the cached lookup another without. You would use expressions on the precedence constraint to decide which one to execute during runtime.
Ravi,
That isn't necessarily true. Right-clicking on the background of the control flow and selecting properties will allow you to access the expressions box. In that expressions box, if a data flow component supports expressions, it will appear in that list.
Took the words right out of my mouth Phil 
In case anyone is interested, expressions on data-flow components was virtually the very last feature that was added to the product prior to RTM. All components CAN support expressions on their custom properties, the component developer decides whether or not it WILL by setting IDTSCustomProperty90.ExpressionType which is set to one of the values of the DTSCustomPropertyExpressionType enumeration.
In answer to the OP, CacheType of the Lookup component does not allow its value to be set by an expression, so you can't do what you want I'm afraid. If you want this behaviour changing then go to http://connect.microsoft.com/sqlserver/feedback
-Jamie
|||
Ravi,
That isn't necessarily true. Right-clicking on the background of the control flow and selecting properties will allow you to access the expressions box. In that expressions box, if a data flow component supports expressions, it will appear in that list.
Phil,
I tried it and I can only see package level properties. I'll have to look closely.
But control flow doesn't seem like the right place to me. What if you have multiple instances of a data flow component that supports expressions, how would you know which property belongs to which instance?
|||Phil Brammer wrote: Ravi,
That isn't necessarily true. Right-clicking on the background of the control flow and selecting properties will allow you to access the expressions box. In that expressions box, if a data flow component supports expressions, it will appear in that list.
Phil,
I tried it and I can only see package level properties. I'll have to look closely.
That's because not all components allow you to set their properties using expressions. if they do, then those properties will show up (off the top of my head I know that the SQLCommand property of the Datareader Source component can be set this way so take a look at that).
But control flow doesn't seem like the right place to me. What if you have multiple instances of a data flow component that supports expressions, how would you know which property belongs to which instance?
The path syntax allows for it - as you shall see.
Sounds like good blog material
-Jamie
|||
Phil Brammer wrote: Ravi,
That isn't necessarily true. Right-clicking on the background of the control flow and selecting properties will allow you to access the expressions box. In that expressions box, if a data flow component supports expressions, it will appear in that list.
Phil,
I tried it and I can only see package level properties. I'll have to look closely.
But control flow doesn't seem like the right place to me. What if you have multiple instances of a data flow component that supports expressions, how would you know which property belongs to which instance?
Sorry. Right click on the data flow on the control flow background.
They are "fully qualified." [DFComponentName].[xxxx].[Property]|||
Sorry. Right click on the data flow on the control flow background.
They are "fully qualified." [DFComponentName].[xxxx].[Property]
Cool. I see it now.
In my package, the derived column was the only component that supported this.
This is how it shows up:
[DF component name].[Derived Column Output].[Column Name].[FriendlyExpression]
What does FriendlyExpression mean?
|||Phil Brammer wrote:
Sorry. Right click on the data flow on the control flow background.
They are "fully qualified." [DFComponentName].[xxxx].[Property]
Cool. I see it now.
In my package, the derived column was the only component that supported this.
This is how it shows up:
[DF component name].[Derived Column Output].[Column Name].[FriendlyExpression]
What does FriendlyExpression mean?
That's the name of the property. Don't worry too much about why its called that, just know that it is a custom property that can be affected with an expression. More here: http://msdn2.microsoft.com/en-us/library/ms141069.aspx
Admittedly this takes a bit of getting your head around. Basically you can set an expression using an expression. Or to put it another way, the result of your expression is, in itself, another expression. 
-Jamie
|||
I've found this useful reference for all the custom properties of the stock components.
http://msdn2.microsoft.com/en-us/ms136014.aspx
-Jamie
No comments:
Post a Comment