how to write expression for Derived Column transform

Question

Post reply

how to write expression for Derived Column transform

KoldCoffee

SSCoach

Points: 18971
More actions
May 29, 2013 at 1:59 pm

#275815

I need to filter out all records as follows:
where
[Month 01] is null and
[Month 02] is null and
[Month 03] is null and
[Month 04] is null and
[Month 05] is null and
[Month 06] is null and
[Month 07] is null and
[Month 08] is null and
[Month 09] is null and
[Month 10] is null and
[Month 11] is null and
[Month 12] is null
The above is t-sql. I want to do in Derived Column. Tried the following but it's not parsing:
[Month 01] !=NULL &&[Month 02] !=NULL] && etc.
How to do the same in SSIS in Derived Column transform?

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

Maybe try something like

Isnull([Month 01]) && Isnull([Month 02]) etc

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

KoldCoffee SSCoach Points: 18971 More actions · Answer 2

Phil, turns out that Derived Column is not for such kind of work because I am needing to filter rows and not handle them ie. concatenate, rename, test a condition, ie derive.

need to find a transformation which do the equivalent work of a WHERE clause...to remove rows from dataset where certain columns evaluate to NULL.

Does anyone think SORT will work?

Phil Parkin SSC Guru Points: 246965 More actions · Answer 3

Conditional Split is the task you need. Add it after the derived column from the previous post. Send all NULLs to one output and everything else to the other. Then use only the 'everything else' output.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

KoldCoffee SSCoach Points: 18971 More actions · Answer 4

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.

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:-)

Still.....if I weren't unpivoting then I wouldn't have a solution. I wonder....how would I do the equivalent of this?

where

[Month 01] is null and

[Month 02] is null and

[Month 03] is null and

[Month 04] is null and

[Month 05] is null and

[Month 06] is null and

[Month 07] is null and

[Month 08] is null and

[Month 09] is null and

[Month 10] is null and

[Month 11] is null and

[Month 12] is null

Phil Parkin SSC Guru Points: 246965 More actions · Answer 5

Great!

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

KoldCoffee SSCoach Points: 18971 More actions · Answer 6

Phil, I modified my post while you were answering. I'm not placing the burden of this question on you, but I decided to push it a little further (if you care to look :-)) No hurry!

lnoland SSCommitted Points: 1753 More actions · Answer 7

KoldCoffee (5/30/2013)
Still.....if I weren't unpivoting then I wouldn't have a solution. I wonder....how would I do the equivalent of this?
where
[Month 01] is null and
[Month 02] is null and
[Month 03] is null and
[Month 04] is null and
[Month 05] is null and
[Month 06] is null and
[Month 07] is null and
[Month 08] is null and
[Month 09] is null and
[Month 10] is null and
[Month 11] is null and
[Month 12] is null

How about:

WHERE COALESCE([Month 01], [Month 02], [Month 03], [Month 04]

, [Month 05], [Month 06], [Month 07], [Month 08], [Month 09]

, [Month 10], [Month 11], [Month 12]) IS NOT NULL

- Les

KoldCoffee SSCoach Points: 18971 More actions · Answer 8

Hmmm, certainly looks promising, but I don't see COALESCE() available in SSIS transforms. Do you?

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 9

KoldCoffee (5/29/2013)
I need to filter out all records as follows:
where
[Month 01] is null and
[Month 02] is null and
[Month 03] is null and
[Month 04] is null and
[Month 05] is null and
[Month 06] is null and
[Month 07] is null and
[Month 08] is null and
[Month 09] is null and
[Month 10] is null and
[Month 11] is null and
[Month 12] is null
The above is t-sql. I want to do in Derived Column. Tried the following but it's not parsing:
[Month 01] !=NULL &&[Month 02] !=NULL] && etc.
How to do the same in SSIS in Derived Column transform?

Just a thought... if the data were normalized instead of pivoted, you wouldn't even have the NULLs to deal with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 10

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 SSCoach Points: 18971 More actions · Answer 11

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 SSCommitted Points: 1753 More actions · Answer 12

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 SSCoach Points: 18971 More actions · Answer 13

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 Newbie Points: 7 More actions · Answer 14

try to use conditional split rather then using derived column transformation. check if this works...