I've got a number of employees and I would like to send an email of each employee's tasks each day based on the contents of a table. I've got a job that will query, format, and send an email. I was not able to find something to do a 'for/each' type of iteration over, for example, the results of a query, so I set up a job that sets a variable to one employee's id, then runs the email job which looks at the variable, then repeats those steps for each employee. A little cumbersome, but doable. The problem is that since the email job runs out-of-process it only gets triggered the first time, then the original job finishes before the email job does.
Does anyone have a suggestion for a better way to go about this? It takes seven tasks to generate and send the email, I'd like to avoid building a job with all the tasks repeated for each employee.