I have a sql task in a ssis package loops through each customer and emails a file. So for every iterateion I need to create a list of To Recipients, CC, and BCC with the semicolon between the addresses. I was trying a case statement but I do not know if that will work?
So For Every ONE Customer, I want to create one TO, CC, and BCC Columns which gets passed to a pckage variable - then used when sending email.
result for [To] would be something like = email@example.com; firstname.lastname@example.org ; email@example.com - so that it emails one time properly
This looks like a task for a three-fold application of the XML PATH hack to concatenate lists.
I'm not yet awake, otherwise I would have a go, but if you're in a hurry, google "XML PATH STRING", and you should find something helpful.
Do one sub-query for each of "To", "CC", "BCC", and that should sort you out.