i am run a stored procdure using Execuate SQL Task i want to log inserted count and Updated Count done be stored procdure

Hi All,

I am run a stored procedure using Execute SQL task in, I want to log information of number of record inserted update in my table. I want to enable SSIS logging after from where I get information number of record inserted update.

I am using SQL server 2008R2 standard edition.

Regards,

Manish

September 8th, 2015 1:05am

Hi Manishchal,

@@ROWCOUNT will show the number of rows affected by the most recent statement 

Example

CREATE PROCEDURE UpdateTables
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @RowCount1 INTEGER
    DECLARE @RowCount2 INTEGER
    DECLARE @RowCount3 INTEGER
    DECLARE @RowCount4 INTEGER

    UPDATE Table1 Set Column = 0 WHERE Column IS NULL
    SELECT @RowCount1 = @@ROWCOUNT
    UPDATE Table2 Set Column = 0 WHERE Column IS NULL
    SELECT @RowCount2 = @@ROWCOUNT
    UPDATE Table3 Set Column = 0 WHERE Column IS NULL
    SELECT @RowCount3 = @@ROWCOUNT
    UPDATE Table4 Set Column = 0 WHERE Column IS NULL
    SELECT @RowCount4 = @@ROWCOUNT

    SELECT @RowCount1 AS Table1, @RowCount2 AS Table2, @RowCount3 AS Table3, @RowCount4 AS Table4
END

Free Windows Admin Tool Kit Click here and download it now
September 8th, 2015 2:21am

You can have a permanent LOG table and insert the data as Milan suggested to that table with current day...
September 8th, 2015 7:09am

hi,

i don't want my own log(@@row count), Execute SQL TASK  is a part of SSIS, i want to use SSIS logging.

Regards,

Manish

Free Windows Admin Tool Kit Click here and download it now
September 8th, 2015 8:04am

Hi Manish,

SQL Server Integration Services includes log providers that you can use to implement logging in packages, containers, and tasks. With logging, you can capture run-time information about a package, helping you audit and troubleshoot a package every time it is run. But it only including some fixed events that can be logged for the Execute SQL Task in the Log Details, it doesnt include the inserted count and updated count for the stored procedure in Execute SQL Task.

So to work around this issue, we can enable package logging with SSIS log provider for SQL Server, then use two variables to store the inserted count and updated count for the stored procedure. Then we can get the variable values from Event Handler, and then insert the records for variable values to the [dbo].[sysssislog] table.

The following blog about how to log SSIS variable values during execution in the Event Log is for your reference:
https://aalamrangi.wordpress.com/2014/12/11/how-to-log-ssis-variable-values-during-execution-in-the-event-log/

Thanks,
Katherine Xiong

September 10th, 2015 10:43pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics