Send Mail on key words

  • 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?

  • 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

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

  • 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

  • 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

  • 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