I’m fairly new to SSIS.

I have a pretty complex SQL query (that I don’t want to replicate & maintain 3 times), whose result is a table with 2 columns: emailAddress, formLetterNumber

What I want to do is

for each row returned:
    if formLetterNumber == 1
           send form letter #1 to that emailAddress
    if formLetterNumber == 2
           send form letter #2 to that emailAddress
    if formLetterNumber == 3
           send form letter #3 to that emailAddress  

For various business reasons, I don’t expect there to be more than 3 form letters.

What I’ve done:

• On the Control Flow, I’ve put an Execute SQL component. (so far, so good)
• I connected it to a Foreach component
• In the Foreach component, I put a Send Mail Task enter image description here This works if there’s only 1 type of form letter. This single-form-letter package runs without any problem.

But now I want to do a Conditional Split so I can divide the flow to 3 different Send Mail Tasks, depending on the formLetterNumber returned by the SQL. But the Conditional Split is on the Data Flow toolbox, and the Send Mail Task is only on the Control Flow toolbox, and I can’t figure out how to move the data from one component to another. See image for what I’m trying to do: enter image description here, enter image description here

I’ve looked on this site, and on YouTube, and either the solution is not there, or I’m asking my question the wrong way. C# and scripting is an option, but purchasing other software is not. Thank you in advance for your suggestions.