Search code examples
sqlsql-serversql-server-2008ssisssis-2008

How to count rows in SSIS based on specific conditions?

I have a Stored Procedure in SQL Server 2008 like below.

ALTER PROCEDURE myStoredProcedure
        @Id int,
        @hin varchar(30),
        @checkValue varchar(30),
        @CounterDeceasedPatients int=0 OUTPUT

insert into myTable
values (@Id, @hin, GETDATE())

if (@checkValue is not null)
BEGIN
    set @CounterDeceasedPatients = @CounterDeceasedPatients + 1;
    update myTable
    set hin= @checkValue
    where Id = @Id

RETURN;
END

I am calling this SP via SSIS, by using an OLE DB Command in Data Flow, which enables each rows in my file go to the SP - with the sql command: EXEC [dbo].[myStoredProcedure] ?,?,?. (The order of data (?) in my file is: Id, hin, checkValue)

What I want to do is to count how many different records (different rows) entered the if condition in my SP. SO I believe need to place a "row counter" somewhere, filtering its usage where @checkValue is not null. But I couldnt find it how. I am a newbie in SSIS, so I appreciate if someone helps me to figure this out. Thanks.

EDIT: I am trying to select only @checkValue as an input parameter for my ROW COUNT, but it is giving error: enter image description here

EDIT2: I updated my SP. I added "CounterDeceasedPatients" variable as Int32 in SSIS and assigned it to 0. My sql execute command is: EXEC [dbo].[myStoredProcedure] ?,?,?,?,CounterDeceasedPatients

This is giving me the error:

Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E07 Description: "Error converting data type nvarchar to int.".

When I use EXEC [dbo].[myStoredProcedure] ?,?,?,?,CounterDeceasedPatients output as SQL command, then I receive the error:

Description: "Cannot use the OUTPUT option when passing a constant to a stored procedure.

I need help.

Solution

  • Use a script transformation and a DataFlow-level package variable.

    Create the int-type variable with a default of 0, and in the script transformation, increment the variable if checkvalue is not null for the incoming row, and then use the value of the variable to set the value of your counter column.

    Note that I am suggesting this INSTEAD of trying to update the counter with an OUTPUT variable in your stored procedure, and not as a way of trying to get that idea to work.