Post reply

Send multiple emails in SSIS from resultset

  • September 15, 2011 at 5:42 pm

    #390418

    Hi everyone. Hoping to find some help.

    I have a table with an inventory in it. Basicly, ItemName, RequesterEmailAddress, ExpirationDate.

    I would like to make an SSIS package that runs once each day and sends an email to each RequesterEmailAddress when the ExpirationDate is reached.

    So basicly if there are three rows that expire today, SSIS would send three emails, one to each address.

    Not sure how to set up something like this. Would it be something like this:

    SELECT * from Table where ExpirationDate = getdate()

    then make a ForEachLoop send mail task from a variable?

    Thanks for reading.

    Howard

  • September 15, 2011 at 5:47 pm

    #1382325

    Yes do a date comparison - but you will need to specify just the date part of that getdate() and ignore the time part.

    As for the ForEachLoop - yes. Spot on with that.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • September 15, 2011 at 5:53 pm

    #1382329

    Thanks for replying. I have the SELECT worked out but I don't know how to write multiple rows to a variable and then create the foreachloop to handle each row. Do you know of any tutorials or examples?

  • September 15, 2011 at 5:58 pm

    #1382332

    PHXHoward (9/15/2011)

    Thanks for replying. I have the SELECT worked out but I don't know how to write multiple rows to a variable and then create the foreachloop to handle each row. Do you know of any tutorials or examples?

    This one's pretty good. You need to use the recordset destination in the dataflow to easily fill it.

    http://www.select-sql.com/mssql/loop-through-ado-recordset-in-ssis.html

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • September 15, 2011 at 5:59 pm

    #1382333

    Here is a tutorial on that

    http://whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • September 15, 2011 at 6:01 pm

    #1382336

    Perfect! Thank you Jason.

  • September 15, 2011 at 6:02 pm

    #1382337

    You're welcome.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • September 15, 2011 at 6:02 pm

    #1382338

    Thanks Evil Kraig F. Much appriciated.

  • September 15, 2011 at 9:25 pm

    #1382364

    It is working now. Thanks for the tutorials.

    Howard

  • September 15, 2011 at 9:27 pm

    #1382367

    That is good to hear. Glad we could help.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply