SSIS - Data Cleanup help please

Question

Post reply

SSIS - Data Cleanup help please

paul.hermans 47816

SSC Veteran

Points: 283
More actions
January 16, 2012 at 10:57 am

#249093

I am new to SSIS so this may be a simple question, but here is what i have.
I migrating from an old data model to a new one, and in the process i have found a few problems.....the one i have right now is that in the original model i have a table like this:
PersonID (int)
Name (string)
Gender (string)
The Gender field is filled with a variety of values that all indicate either male or female (F,M, Male, Female, Boy, Girl, B, G)
The new model is like this:
PersonID (int)
Name (String)
GenderID (int)
then in a lookup table there are two rows with
Male = 1
Female = 2
What I want to do in SSIS is to say " if the value of the input column Gender is "M" or "Male" or "Boy" or "B" then make the output column named GenderTypeID = 1
Also want to say "If value of Gender = "F" or "Female" or "girl" then make output column named GenderTypeID=2
I thought i could do this with a lookup but just don't see how to do so. Hoping somebody can help me figure the best way to do this.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Brigadur SSCrazy Points: 2097 More actions · Answer 1

Hi,

You could use the Derived Column transformation in Data Flow to achieve this.

Regards

Istvan

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 2

Check this link. It has plenty of useful information if you are a beginner. 😉

My blog

paul.hermans 47816 SSC Veteran Points: 283 More actions · Answer 3

I was sort of coming to that conclusion, but am uncertain how to do it.....

Do I make a single derived column that replaces the value of the Gender field in the original table as follows:

( UPPER(Gender)=="MALE" || UPPER(Gender)="M" || IsNull(Gender) ) ? "Male" : "Female"

if i understand correctly, this should make all the records say "Male" or "Female"......and if i need to I can do a lookup on those valuse.

Let me know if I'm on the right track please.

Thanks

Revenant SSC-Forever Points: 42467 More actions · Answer 4

paul.hermans 47816 (1/16/2012)
I was sort of coming to that conclusion, but am uncertain how to do it.....
Do I make a single derived column that replaces the value of the Gender field in the original table as follows:
( UPPER(Gender)=="MALE" || UPPER(Gender)="M" || IsNull(Gender) ) ? "Male" : "Female"
if i understand correctly, this should make all the records say "Male" or "Female"......and if i need to I can do a lookup on those valuse.
Let me know if I'm on the right track please.
Thanks

Yes, you are, but you will have to also handle "Boy", per your original requirement.

Brigadur SSCrazy Points: 2097 More actions · Answer 5

Hi,

Yes you are on the right track, another way to put it is:

([Gender]=="M"?"Male" : ([Gender]=="Male"?"Male" : ([Gender]=="Boy"?"Male" : ([Gender]=="B"?"Male" : "Female"))))

However this is a bit messy and not completely secure. I would rather recommend a lookup transform. However if this is to be only run once and you are confident about the data, then go for this simpler solution.

Regards

Istvan

paul.hermans 47816 SSC Veteran Points: 283 More actions · Answer 6

I was trying to figure out how to use a lookup transform, but it doesn't seem obvious.

how would i do that?

It really is a 1 or 2 time thing that once done will be discarded though, so if it is hard then I'll go with the first solution

Revenant SSC-Forever Points: 42467 More actions · Answer 7

paul.hermans 47816 (1/16/2012)
I was trying to figure out how to use a lookup transform, but it doesn't seem obvious.
how would i do that?
It really is a 1 or 2 time thing that once done will be discarded though, so if it is hard then I'll go with the first solution

A click by click cache lookup walkthrough is at http://technet.microsoft.com/en-us/library/ms137820.aspx.

I think that for the first iteration you might try the solution proposed by Brigadur.