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