Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

how to write expression for Derived Column transform Expand / Collapse
Author
Message
Posted Sunday, June 2, 2013 11:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:34 AM
Points: 7,097, Visits: 12,601
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
Post #1459084
Posted Monday, June 3, 2013 7:10 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 11:12 AM
Points: 631, Visits: 1,470
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.
Post #1459254
Posted Monday, June 3, 2013 9:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 3:31 PM
Points: 114, Visits: 548
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.

- Les
Post #1459359
Posted Thursday, June 6, 2013 10:16 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 11:12 AM
Points: 631, Visits: 1,470
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.
Post #1460944
Posted Friday, June 7, 2013 10:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 11:13 PM
Points: 7, Visits: 38
try to use conditional split rather then using derived column transformation. check if this works...
Post #1461257
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse