Is there a better expression editor available for BIDS SSIS 2008R2?

1
votes

I've found that editing expressions within a grid column in the BIDS tool (for SSIS 2008R2) is highly impractical for anything but the simplest expressions.

Eg refer the "Expression" column in the "Derived Column Transformation Editor".

Is there an "advanced" editor available within BIDS, or a 3rd party tool, that provides the same drag/drop functionality for available columns/variables/functions, but has enough screen real-estate in the text-editor component such that I can actually see the expression I'm building?

I've come across this: http://expressioneditor.codeplex.com/ . While this looks excellent, from what I can tell it's not actually integrated into BIDS.

This http://bidshelper.codeplex.com/ includes the above editor, but only seems to make it available for expressions found within the Control Flow level (not within Data Flows where I most need it).

There's also a teasing "Open IME" option available in the right-click context menu when editing an expression - but I can't find an Input Method Editor suitable.

Any help appreciated!

1
ssisbids
Nothing prevents you from creating Variables and using the rich expression editor (control flow one) to build your expressions and then paste them into the needed locations of a data flow. You'll need to update variable references to column references but that's a simple switch. – billinkc
@billinkc Exposing my lack of SSIS knowledge here - are you saying that variables (created at the control-flow level) effectively act as "functions"? ie they a) can reference columns available within the data-flow, and b) are re-evaluated row-by-row each time they're referenced? If so - this is a brilliant work around. – Sepster
Perhaps an example will explain (comments suck). RIGHT(@[User::MyVariable], 4) vs RIGHT([MyColumn], 4) I'd use the control flow expression editor to make my expression work right for static data and then fix to work with columns when I was satisfied with the logic – billinkc
Ah, I see - you're suggesting I use the rich expression editor to build the "framework" of the expression, and then update as appropriate. Yes, good suggestion in general and I'll do that for shorter functions, thanks. The problem I have that actually prompted this question, though, is for long expressions (even if they're dead simple eg concatenating a string of eg 10 error columns into one); The act of actually finding the point to drag/drop the column name is fiddly. And some of these column names are long, so even just using a text editor to do it is cumbersome and error-prone. – Sepster
PS if you add an answer with that suggestion I'll upvote it (although won't accept as it's not ~quite~ what I need although very helpful, thanks!) – Sepster

1 Answers

1
votes

Nothing prevents you from creating Variables and using the rich expression editor (control flow one) to build your expressions and then paste them into the needed locations of a data flow. You'll need to update variable references to column references but that's a simple switch.

Perhaps an example will explain (comments suck). RIGHT(@[User::MyVariable], 4) vs RIGHT([MyColumn], 4) I'd use the control flow expression editor to make my expression work right for static data and then fix to work with columns when I was satisfied with the logic.

The rule of thumb I go by is if an Expression takes more room that the window can show, then I should probably look at either using a Script Component to perform the logic or add multiple Derived Column Components in series and build my Expressions piecemeal.