I have a request to send emails the first of every month for each department that hasn't submitted their hours. I have a query that generates a list of email addresses, the email subject, and the email body. However, in the Data Flow SSIS Toolbox there is not a "Send Email Task" option like there is in the Control Flow.
How can I send emails based off of my query results?
Best Answer
As @sp_BlitzErik suggests, this is simpler to do in SQL directly. If it needs to be part of an SSIS package for some reason, write a SQL script to do the job, and put that in the package as an "Execute SQL" task. And yes, this is actually one of those rare times where using a cursor actually makes sense.
However, if for some reason you can't use
sp_send_dbmail, you can do this in SSIS - just not in the data flow.Execute the query that generates your list in an "Execute SQL" task, and put the resultset in an object. You may need to modify your query to make sure there's only one resultset - use
SET NOCOUNT ON, etc. Then, have a "Foreach" Loop run over the object, putting the various column values in variables. Inside the "Foreach" loop, put the "Send Email" task. Set up all the necessary values as expressions (the last tab in the task editor window), with each expression being the appropriate variable populated by the loop.