Checking data and emailing the results....

Hello,

I am trying to use “SQL Server Business Intelligence Development Studio” (what is the short name for this) to complete a check and then possibly email me depending on what happens.

The check is around two tables that I have. There is a chance that they will become out of sync with each other and so I want to know when a field (ACCOUNTID) has a value that is in one table and not the other. I can do the check but I would like the package to email me when there is a problem (and if possible email me the details) and do nothing when everything is OK.

Can anybody give me some hints to point me in the correct direction as I just seem to be chasing my tail at the moment,

Thanks,

Tyrone Fay wrote:

Hello,

I am trying to use “SQL Server Business Intelligence Development Studio” (what is the short name for this) to complete a check and then possibly email me depending on what happens.

BIDS!!

Tyrone Fay wrote:

The check is around two tables that I have. There is a chance that they will become out of sync with each other and so I want to know when a field (ACCOUNTID) has a value that is in one table and not the other. I can do the check but I would like the package to email me when there is a problem (and if possible email me the details) and do nothing when everything is OK.

Can anybody give me some hints to point me in the correct direction as I just seem to be chasing my tail at the moment,

Thanks,

How about loading all of the offending data into a flat file and then use the SendMail task to send that file to you as an attachment?

-Jamie

|||I like the idea... thank you,|||

Hi,

This is all working, but I am getting the file sent even when there are no values in it, which is causing some aggravation.

One solution I have tried is to wrap it with an error so that if there is no data to be returned the process errors and then the email is never sent. Yet this does mean that if you look at the agent it is not clear if the agent has an error or there was data to be sent and therefore this has caused the agent to error.

Does anybody have any ideas on a cleaner way (if possible) to make sure the file is only sent when there is data to be sent?

Thanks again,

|||

Instead of a file I use a variable.. then I have a script task to check if the variable is empty or not and crafting an appropriate message body based on the value of the variable before sending the email. I don't use a file at all.. I put all the errors etc into the message body.

You should be able to adapt this method for an attachment or not as well - just have the script output a value, have 2 email objects: one that goes with an attachment and one that doesnt and use an expression (or "Expression and Constraint") between them and the script. If its simple you can just do it all in the expression and skip the script object altogether.