SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to write expression for Derived Column transform


how to write expression for Derived Column transform

Author
Message
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14813 Visits: 14396
KoldCoffee (5/30/2013)
That worked. Thanks Phil. I was trying to accomplish before unpivot.
In my dataset I sometimes have rows, where all 12 of the 12 rows that I am unpivoting (using unpivot transform) contain a NULL. I wanted to filter those out before the unpivot.

If I am understanding, then this could be done by passing the data through an Asynchronous Script Component Transformation in your Data Flow. This way, you would keep track of and construct the aggregated rows manually in .NET and if any of the 12 incoming rows failed to meet your criteria, you would not add a new aggregated row to the Output for that group of incoming rows. In effect you would be doing the work in you are currently doing with a Conditional Split plus an Unpivot inside of a single Script Component. More work, but it will probably perform better.

However, if I use conditional split after the unpivot, on the aggregated column, it excludes those rows I'm after. So for now I am fine:-)

I think what you have might be the simplest way to solve the problem case using the out of the box SSIS components.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
KoldCoffee
KoldCoffee
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1519 Visits: 1905
good, I took a glance and looks promising.
"This syntax originated in C and C#...." no wonder! It is not unlikely that I will read, try, and fail. But, I do want to understand the syntax and be self sufficient with much used Derived Column.
lnoland
lnoland
Old Hand
Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)

Group: General Forum Members
Points: 320 Visits: 821
KoldCoffee (6/1/2013)
Hmmm, certainly looks promising, but I don't see COALESCE() available in SSIS transforms. Do you?

I apologize -- I should not have even commented on the thread. I don't actually use SSIS and was just presuming that it would have included the normal T-SQL functions but such speculation wasn't very helpful. Sorry. Blush

- Les
KoldCoffee
KoldCoffee
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1519 Visits: 1905
Inoland,that's no problem. I suspected that and it wasn't a problem. It's the sanctimonious and beer belly ones that irk me.
foreverjpk
foreverjpk
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 38
try to use conditional split rather then using derived column transformation. check if this works...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search