Post reply

Check table for Duplicates, send email of duplicates and force failure

  • November 12, 2017 at 12:09 pm

    #326171

    Hi,

    I have the below SQL script, which will insert 7 records to the table stageOrderTrans.

    The OrderId 5 has two records which means it is a duplicate, therefore is it possible to create a Stored Procedure that will send a email to Joe.Bloggs@anywhere.com notifying him within the Email Body which OrderId or OrderId's this is.

    I also want the stored procedure to Force to fail after the Email step is executed, this is because the stored procedure will be called within a SSIS package so I want this to fail. 

    If no Duplicates found then No Email to be sent and Stored Procedure is a success.

    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[stageOrderTrans](
        [OrderId] [int] NOT NULL,
        [Qty] [int] NOT NULL,
        [TransDate] [datetime] NOT NULL
    ) ON [PRIMARY]

    GO

    Insert into stageOrderTrans values (1,10,'08-OCT-2015')
    Insert into stageOrderTrans values (2,15,'08-OCT-2015')
    Insert into stageOrderTrans values (3,4,'09-OCT-2015')
    Insert into stageOrderTrans values (4,16,'09-OCT-2015')
    Insert into stageOrderTrans values (5,4,'09-OCT-2015')
    Insert into stageOrderTrans values (5,40,'09-OCT-2015')
    Insert into stageOrderTrans values (6,5,'09-OCT-2015')

    --Check for Duplicates
    Select
        orderId
        ,COUNT(*) cnt
    From stageOrderTrans
    Group By
        orderId
    Having COUNT(*)>1    

    /*
    If cnt >1
    Then send an email of which OrderId's is the Duplicate to Joe.Bloggs@anywhere.com
    and Force Stored Procedure to Fail i.e Select 1/0
    If NO DUPLICATES just do a Select 1 so the Stored Procedure does not fail and the next step of the SSIS package will be called
    */

  • November 13, 2017 at 5:16 am

    #1967744

    What do you want to fail?  You've already staged the data with a duplicate.  What happens to that staged data?

    If all you want is to stop further processing, you can do that in the SSIS package itself.  Run the query that looks for duplicates in an SQL Task, and store it in a variable.  Then conditionally branch from that task:  If dups are found, use a SQL Task to write the email (see sp_send_dbmail) and stop.  If no dups were found, branch to the rest of the process.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply