March 17, 2009 at 8:50 am
I have an SSIS package which imports data into several tables.
Can I create an SP which uses Database mail to send an email when the word "SELL" is imported into a specified column?
Or is there an easer way to do this?
March 17, 2009 at 8:55 am
SSIS can send e-mails on its own, but I usually find DBMail easier to use.
Depending on what you want in the e-mail, you could start the proc with "if exists (select * from dbo.MyTable where ColX like '%sell%')".
To go into more detail than that, I'd need to know more about the data and what you want to do with the e-mail.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2009 at 9:17 am
1. SSIS packages imports data from a flat file into 3 tables
2. The table im interested in is tblClients, this contains 3 columns
Client_ID, Client_Name, Comments
I would like to send an email to myself if the word "SELL" appears in the Comments column.
Sorry is I was not clear first time around.
March 18, 2009 at 7:02 am
Is there a time stamp of some sort that says when the data in that table was imported?
Do you just need an e-mail that says, "Had "SELL" in comments today", or does it need to say more than that? Does it need to have any data in it from the table?
If there's more than one row, do you want more than one e-mail, or just one e-mail per day?
How often does the SSIS package run? Daily?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 18, 2009 at 8:55 am
Is there a time stamp of some sort that says when the data in that table was imported?
I am importing the data from the MSDB table sysjobhistory, so yes, there is a date column
Do you just need an e-mail that says, "Had "SELL" in comments today", or does it need to say more than that? Does it need to have any data in it from the table?
Yes just a shprt email to say "Sell clientID XXXXX", so yes it will need to insert the client ID in the email too
If there's more than one row, do you want more than one e-mail, or just one e-mail per day?
I would prefer one email, with all the "SELL" information in for each row..
How often does the SSIS package run? Daily?
Package would run daily
March 19, 2009 at 7:10 am
It will look something like this:
declare @Body varchar(max);
select
@Body =
coalesce(
@Body + char(10)+char(13) + 'Sell clientID ' + cast(Client_ID as varchar(10)),
'Sell clientID ' + cast(Client_ID as varchar(10)))
from dbo.tblClients
where -- I don't know what your date column is. You'll need to add that here.
exec msdb..sp_send_dbmail
@recipients = 'YourEmail@YourProvider.com',
@subject = 'Sell IDs',
@body = @Body;
I'm not clear on what would go in the Where clause. I'm not sure what the relationship is between the tables you're talking about, where the date column is.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy