CODEWITHSUNDEEP
ssis
Mathematics
Mathematics

Reputation: 7618

SSIS combine 2 sets of columns into one based on given data

I have following ONE dataset with additional columns not mentioned for ease of reading,

Address1_PostCode         Address2_PostCode         
Address1_Line1            Address2_Line1        
Address1_Line2            Address2_Line2         
Address1_Country          Address2_Country

Desired output is,

Address2_PostCode         
Address2_Line1                    
Address2_Line2                     
Address2_Country

Here is what I am trying to do,

I already searched on my own and I can see there is functionalities like replace column to add new and replacenull function, but I am not able to understand them enough to achieve my goals

Upvotes: 2

Views: 1264

Answers (2)

KeithL
KeithL

Reputation: 5594

I would suggest an alternate data model which allows you to keep your original data intact.

  1. Add a multicast (this will allow the data to duplicate)
  2. Have one flow handle the normal non-address flow
  3. Add a new flow to handle person address data.
  4. Add a script task (this will be used to normalize the addresses
  5. Mark the key as input and all the address columns as input
  6. Create a new output (call it Address) with PersonKey, AddressType, AddressLine1, AddressLine2, PostalCode, Country

  7. Add this simple code.

    AddressOutputBuffer.AddRow();
AddressOutputBuffer.PersonKey = Row.PersonKey;
AddressOutputBuffer.AddressType = "Address1";
AddressOutputBuffer.AddressLine1 = Row.Address1_Line1;
... (Add the rest in here)

AddressOutputBuffer.AddRow();
AddressOutputBuffer.PersonKey = Row.PersonKey;
AddressOutputBuffer.AddressType = "Address2";
AddressOutputBuffer.AddressLine1 = Row.Address2_Line1;
... (Add the rest in here)

  8. Write this new Person Address info to a new table (you can build whatever logic you want to the queries you write or you can create a view to handle your specific logic.)

NOTE: You may need to null handle the code above Ex: 

AddressOutputBuffer.AddressLine1 = !Row.Address1_Line1_IsNull?Row.Address1_Line1:"";

Upvotes: 1

EzLo
EzLo

Reputation: 14189

Add a Derived Column transformation that has a new column expression with a boolean result that will check whether all Address1 fields are empty/null or not.

enter image description here

Add another Derived Column transformation after that one (unless you want to repeat the validation expression on each column) that checks for this bool result and reassigns the proper field from either Address1 or Address2, on each field you need.

enter image description here

On this last step you can either add new columns to the flow or overwrite existing ones, just make sure that you are using the ones being checked.

Upvotes: 2

Related Questions