Welcome to EMC Consulting Blogs

PingBack from http://www.pythian.com/blogs/637/log-buffer-66-a-carnival-of-the-vanities-for-dbas

Jamie,

I am a little unsure about your comment

"

Expressions on components can be really useful. The best use that I know of is that the SQL command used in a Datareader component can be changed at execution time. The component itself doesn't provide the ability to change the SQL command from a variable like the OLE DB Source component does so putting an expression on the component is the only way to accomplish it.

"

I am actually building SQL commands on the fly for DataReaders data Flows using the expression for each datapull on the control flow  by combining strings with variables at runtime.

Can you please clarify this? is that has something to do with the limitation on variables values set at runtime AFTER pre-validation?

Cheers,

Philippe

Hi

I am attempting to use the sqlcommand property with a Datareader to implement a query for an incremental load of a table.  I read the last log id tranferred and add it to the WHERE clause at runtime.  This works fine for most of the tables but for one monster table (large number of columns not rows) the expression is too large for the expression editor. (over 4000 char)  Is it possible to bypass the expression and set [ComponentName].[PropertyName] in a script task?

One thing about using expressions, the property that you define with the expression looks just like it was hard-coded in the component. This is particularly deceiving when you've used an expression for a SQL statement as you can open and even edit the SQL, but it doesn't save the edit because the expression overrides it.

So, as a trick, I've taken to putting the comment "-- NOTE: This is defined by an expression" as the first line in any of my SQL. Now, when I open the component, I have a reminder of where to look to edit the code. It's proven very helpful ...

I think Jim's commenting the first line of SQL to indicate that it is defined by an expression is great. I've been caught many times trying to figure out why a given SQL change was not "taking" only to realize it was being overwritten (or is that overshadowed) by an expression. Thanks, Jim!