Post reply

I want to schedule an email daily using SSIS writing couple of queries

  • May 4, 2010 at 3:54 pm

    #143039

    I want to schedule an email daily from database having result set of the query :

    SELECT categoryname,brandname,productname,colorname,colorcode,psizename FROM productscolortb_withsize WHERE isnull(available_qty,0) = 0 AND isnull(available_qty_temp,0) = 0 AND hotflag = 'Y' and hotflagemail is null

    and after performing this query for each row selected in above query it will mark hotflagemail as '1' by following query:

    update productscolortb_withsize set hotflagemail = '1' where productname = "&rs1("productname") &" and colorcode = "&rs1("colorcode") &"

    Can anybody please suggest me how can i do this !!

  • May 4, 2010 at 4:11 pm

    #1162148

    Why do you want to use SSIS?

    I would create a stored proc to send the mail and do the update and once I have that running as expected add a job calling that sproc daily at the desired time. This would give you the error handling options for a failed job "for free"...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • May 4, 2010 at 5:16 pm

    #1162170

    That is a really good suggestion, I agree, I don't see any real reason to do this in SSIS..

    CEWII

  • May 4, 2010 at 7:17 pm

    #1162195

    I dont know how to write stored procedure for this like there are two queries involved in this as you see above so, how I am going to do this...

  • May 4, 2010 at 10:30 pm

    #1162214

    Before I answer your question, how many rows are you expecting on average?

    It affects how I tell you..

    CEWII

  • May 4, 2010 at 10:40 pm

    #1162220

    not more hardly in 100's

  • May 5, 2010 at 9:01 am

    #1162530

    my favorite way to do this is by SSRS. it's overkill but i hate making the result set of database mail look nice.

Viewing 7 posts - 1 through 7 (of 7 total)

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