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 = me@ mycompany.com; you@ mycompany.com ; us@ mycompany.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.: http://www.google.co.uk/search?sourceid=chrome&ie=UTF-8&q=xml+path+string