Emailing rowcount from SSIS

  • 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!!!

  • 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

  • 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