November 9, 2018 at 3:47 am
Hi,
I have a requirement to send email in SSIS if the count of source and destination tables doesnt match after upload. The email should include the number of rows affected.
The logic is to copy data from source to a destination table and if the count for the upload for a particular date in source table doesn't match the one in destination then it should delete all rows for that file with that particular date and then send an email with the row count of the records deleted and the name of that particular file.
I have created a query to delete the rows and have to incorporate this within the Stored procedure .
DELETE FROM ARCH_SALES_DIAG_ORDER_REVENUE
WHERE SRC_FILE_NAME IN (
SELECT ARCH.SRC_FILE_NAME FROM
(SELECT SRC_FILE_NAME,SRC_FILE_DATE,COUNT(*) AS ARCH_COUNT
FROM ARCH_SALES_DIAG_ORDER_REVENUE
GROUP BY
SRC_FILE_NAME,SRC_FILE_DATE) AS ARCH
INNER JOIN
(SELECT SRC_FILE_NAME,SRC_FILE_DATE,COUNT(*) AS STAGE_COUNT FROM TEMP_SALES_DIAG_ORDER_REVENUE
GROUP BY
SRC_FILE_NAME,SRC_FILE_DATE) AS STAGE
ON(ARCH.SRC_FILE_NAME=STAGE.SRC_FILE_NAME AND ARCH.SRC_FILE_DATE=STAGE.SRC_FILE_DATE)
WHERE ARCH.ARCH_COUNT<>STAGE.STAGE_COUNT);
RETURN ROW_COUNT;
The problem I am facing now is how to send email based on the row count of the above query and also include the file name which is affected. Could anybody please provide any suggestions ? Thanks.
November 9, 2018 at 4:47 am
You just need to call msdb.dbo.sp_send_dbmail with the right parameters.
November 9, 2018 at 4:59 am
Jonathan AC Roberts - Friday, November 9, 2018 4:47 AMYou just need to call msdb.dbo.sp_send_dbmail with the right parameters.
If your in SSIS you do also have access to the Send Email Task. That only supports Plain Text format emails though, not HTML or Rich Text.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply