April 1, 2011 at 10:59 am
Hi, I have question regarding send mail task in SSIS.
I am trying to have SSIS to send alert email based on number product customer purchase, and I am not sure if this is possible with SSIS.
Our department managers wants to get alerted on items customer purchase, and if certain item sold more than 150 times (throughout whole time), they want to get alerted with item information.
Initially, I was going to just send email report on items with 150 transaction or more but problem is managers want alert, not report. They only want to get alerted when item pass 150 transaction. Once transaction goes over 150 times, they don't want to get alerted again and again for same time.
How would create SSIS package for this?
April 4, 2011 at 5:37 am
Hi,
Actually this can be achieved through number of ways. But what I was thinking will work best when you could create a tracker table in you DB.
Something like this will do.
CREATE TABLE Tracker(ID INT NOT NULL
, track_item_id INT
, track_date DATETIME
, alert_sent BIT)
Create a trigger on the Customer' Purchase table. So whenever any item crosses 150 mark, it will log an entry into the Tracker table with alert_sent flag set as false (or 0) by default. Now whenever you will be running your ssis package it (Execute SQL Task) will check against this table to retrieve all entries whose alert_sent flag is set as false.
Keep this Execute SQL Task inside foreach loop container (looping over dataset object) and file mail for all such rows. After reading you will update the flag to true which will prevent it from any future reads.
Hope this helps
Thanks
__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!
April 5, 2011 at 3:11 pm
Thanks for your input!
Is there anyway I can do this trigger? Our report database is read only and I can't create any view or trigger 🙁
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply