• I do something similar but I insert the row counts into a Table first then I run this code in an Execute SQL Task. FYI I am using SSIS 2012 Server Data Tools.

    DECLARE @rundate VARCHAR(10) = CONVERT(VARCHAR(10),CAST(GETDATE() AS DATE),1),

    @SQL VARCHAR(8000)

    SET @SQL = 'SELECT DatabaseName, TableName,CountOfRows, DateRan FROM Staging.[dbo].[DailyRowCounts_SSIS]

    WHERE CAST(DateRan AS DATE) = ''' + @rundate + ''' AND DatabaseName LIKE ''%Local'' AND TableName IN (''[dbo].[ClaimLedger]'',

    ''[dbo].[Invoices]'',

    ''[dbo].[Claims]'',

    ''[dbo].[JournalEntries]'',

    ''[dbo].[Accounts]'',

    ''[dbo].[Patients]'',

    ''[dbo].[Payments]''

    )

    UNION

    SELECT DatabaseName, TableName,CountOfRows, DateRan FROM Medapoint_

    WHERE CAST(DateRan AS DATE) = ''' + @rundate + ''' AND DatabaseName LIKE ''%Cloud'' AND TableName IN (''[dbo].[ClaimLedger]'',

    ''[dbo].[Invoices]'',

    ''[dbo].[Claims]'',

    ''[dbo].[JournalEntries]'',

    ''[dbo].[Accounts]'',

    ''[dbo].[Patients]'',

    ''[dbo].[Payments]''

    )

    ORDER BY TableName

    '

    EXEC (@SQL)

    exec msdb.dbo.sp_send_dbmail

    @profile_name = 'SSIS',

    @recipients = 'DG_Corporate_DBA_Mail@someemail.com',

    @body = 'The RowCounts for Today Are:.',

    @subject = 'Daily ETL Verification',

    @query=@SQL