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

Question

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

biren

Right there with Babe

Points: 719
More actions
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 !!

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

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]

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

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

CEWII

biren Right there with Babe Points: 719 More actions · Answer 3

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...

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

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

It affects how I tell you..

CEWII

biren Right there with Babe Points: 719 More actions · Answer 5

biren

Right there with Babe

Points: 719

May 4, 2010 at 10:40 pm

#1162220

not more hardly in 100's

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 6

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