none
Comparing one column in 2 tables from 2 different databases in SSIS RRS feed

  • Question

  • Hi.

    As the subject suggests, I am trying to compare 2 tables from two difference databases on the same SQL Server instance.  I have tried this using SSMS using the following piece of code:

    select substring(col1,2,28), * from db1.dbo.tbl1 T where NOT EXISTS
    (
         select left(col2,28) from db2.dbo.tbl2 B WHERE left(B.col2,28) = substring(T.col1,2,28)
    )
    

    As you can see, I am using a LEFT function on both columns to return certain parts of the string in the column specified.

    If the code returns at least one row, I would like to send an email to all concerned to notify there is a column mismatch.

    My questions are:

    1. What is the SSIS equivalent of the code above?

    2. Can I send an email with the offending row(s) in SSIS without inserting the offending row(s) into a table in the Database Engine?

    Thanks in advance. 

    Friday, September 11, 2015 9:53 AM

Answers

  • Hello Warner

    This can be achieved with or without an SSIS package; in fact it can be achieved with just one stored procedure and one extra table.

    Essentially you create a stored procedure along these lines to accomplish this..

    Create Procedure usp_DoStuff AS
    BEGIN
    Declare @RowCount int
    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'DataCheck')
     BEGIN
     DROP TABLE DataCheck
     END
    select substring(col1,2,28), * INTO DataCheckfrom db1.dbo.tbl1 T where NOT EXISTS
    (
         select left(col2,28) from db2.dbo.tbl2 B WHERE left(B.col2,28) = substring(T.col1,2,28)
    )
    Set @RowCount =(Select count (*) From DataCheck)
    If @RowCount >0
    BEGIN
     EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'blah',
     @recipients = 'someone@yourcompany.com;,
     @subject = 'Bad Data',
     @query = 'SELECT * From DataCheck',
     @attach_query_result_as_file = 1 ;
    END
    END


    Please click "Mark As Answer" if my post helped. Tony C.

    Friday, September 11, 2015 12:24 PM