CODEWITHSUNDEEP
ssisforeachmerge
sebagiar
sebagiar

Reputation: 155

Merge columns in Integration Services (SSIS)

In a Data Flow, I have a ADO NET Source which load a table like this:

PersonID, Email
1, "[email protected]"
1, "[email protected]"
2, "[email protected]"
2, "[email protected]"

I need to merge emails from each persons and get a result like this:

PersonID, EmailsArray
1, "[email protected],[email protected]"
2, "[email protected],[email protected]"

How to I do it? Using derived column? a script component? a foreach loop? (in Data Flow doesn't exist). Thanks in advance.

Upvotes: 1

Views: 501

Answers (1)

Jeroen Bolle
Jeroen Bolle

Reputation: 1836

Use an asynchronous script component with something like the following logic:

  1. Sort your data on the ID column.
  2. In the script component, declare a variable that keeps track of the previous id, assign it to the ID column of your input buffer at the end of your script.
  3. For each row in the input buffer, concatenate the email field to a string variable.
  4. Check if the previous ID is equal to the current ID (coming from your input buffer). If it is different, add a row to the output buffer with the previous ID and the concatenated string. Reset the string as empty.

MSDN

Upvotes: 2

Related Questions