Merging 4 columns into one during an SSIS Transformation

Question

Merging 4 columns into one during an SSIS Transformation

davidr-974274

SSChasing Mays

Points: 619
More actions
April 28, 2009 at 4:58 pm

#133594

Hi,
I have an SSIS package written which joins (via Merge Join) multiple tables using their Primary Key relationships to extract the following 4 columns:
LandStatus
BuildingStatus
ComplexStatus
DwellingStatus
The next task is to Merge these 4 columns into one Called "Property Status" I can't see how I can use the Union All task at this point in the package and the MErge task does not seem to help as my four columns are coming from the one input. Can anyone advise ? (Please see image attached...) Thankyou David

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

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

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

Add a derived column transformation after the merge to give you the data as you want.

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

davidr-974274 SSChasing Mays Points: 619 More actions · Answer 2

THanks for the Reply Phil.

Still not sure what you mean; how do I merge/bleed these 4 cols into the one via a derived column task - I am not trying to concatenate their values, but merge/create multiple rows ...

Thanks again

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

OK, I misunderstood the requirement.

Can you give sample before (source) and after (required format) data please - just one example should be enough for me to picture it - thanks.

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

davidr-974274 SSChasing Mays Points: 619 More actions · Answer 4

Phil,

After I have carried out my 3 merge joins, I have the following dataset

LandStatus BuildingStatus ComplexStatus DwellingStatus

davidr-974274 SSChasing Mays Points: 619 More actions · Answer 5

Sorry, hit the enter key - try again

===========================

Phil,

After I have carried out my 3 merge joins, I have the following dataset of 4 columns

LandStatus BuildingStatus ComplexStatus DwellingStatus

-----------------------------------------------------------

LandValue1

LAndValue2 BuildingValue1

LandValue3 BuildingValue2 ComplexValue1

LandValue4 BuildingValue3 ComplexValue2

LandValue5 BuildingValue4 ComplexValue3 DwellingValue1

The result is to merge these 4 columns such that I have just one column:

PropertyStatus

--------------

LandValue1

LAndValue2

LandValue3

LandValue4

LandValue5

BuildingValue1

BuildingValue2

BuildingValue3

BuildingValue4

ComplexValue1

ComplexValue2

ComplexValue3

DwellingValue1

Phil Parkin SSC Guru Points: 246962 More actions · Answer 6

OK, good - that makes it clear. I can think of two ways:

1) Import your data to a staging table in SQL Server and then use a UNION query to get the data as you want

2) Use a Script component to create multiple output rows from a single input row.

Phil

PS if you make a small error or omission, you can edit an existing post rather than adding a new one - just as I have done now.

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

davidr-974274 SSChasing Mays Points: 619 More actions · Answer 7

Thanks for that - yes, might have to look at shipping it out to SQL and restructuring it there.

Rgds

D