How to count rows in SSIS based on specific conditions?

Question

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:

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.

Have you heard for [`Row Count Transformation`](https://msdn.microsoft.com/en-us/library/ms141136.aspx) in SSIS ? — M.Ali, May 30 '16 at 13:06
Yes, I read this page: http://www.techbrothersit.com/2013/07/ssis-how-to-use-row-count.html But I couldnt manage Row Count working, and couldnt see where to filter it. — Eray Balkanli, May 30 '16 at 13:14
Add an SSIS variable `@rowcount`. Initialise it to 0. Create an output parameter in your stored procedure and modify your stored procedure so it adds one to it inside the `if`. Map your SSIS variable to that output parameter so each call it passes in the value that was potentially modified in previous calls. — Martin Smith, May 30 '16 at 14:45
I updated my question based on ur comments Martin. I appreciate if you help. — Eray Balkanli, May 30 '16 at 16:22

score 0 · Accepted Answer · answered May 31 '16 at 14:40

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.

How to count rows in SSIS based on specific conditions?

1 Answers1