Site icon Good BB

SSIS – send different emails to employees depending on employee situation

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:

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 hereenter 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.

Exit mobile version