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 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.
It is possible to use Precedence Constraints in the SSIS Control Flow.
Precedence constraints support expressions. If we choose expressions in SSIS precedence constraints, it evaluates the expression and if the expression is true, then the constrained executable runs.
So for your case you would need three precedence constraints with an expression along the following line:
@formLetterNumber == 1
Next precedence constraint expression would be
@formLetterNumber == 2
Good article on the subject: Overview of SSIS Precedence Constraints
As end result, it is possible to compose the process as follows: