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.

CodePudding user response：

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:

1. Add an empty Sequence Container inside Foreach Employee Container.
2. Out of Sequence Container add three Precedence Constraints described above.
3. Each Precedence Constraint ends up with its own Send Email task for a specific type of form letter.