September 18, 2014 at 1:19 am
Hi All,
I need help in automating the following process. Currently I import data from Oracle to SQL server then I will receive an email once the job is completed.
What I need to include on the email is the number of records received from Oracle to SQL. See the example of the email format would like.
Please see attached current and desired outputs
Thanks
It's better to fail while trying, rather than fail without trying!!!
September 18, 2014 at 4:05 pm
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
September 18, 2014 at 10:34 pm
Thanks, will give it a try. I also use 2012 Data Tools
It's better to fail while trying, rather than fail without trying!!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply