How to receive "output" variable of SP in SSIS to count how many data entered into conditional IF

Question

I have an SQL Procedure like below:

CREATE PROCEDURE [dbo].[ExampleSP]
    @ID int,
    @name varchar(30),
    @Counter int output
AS
BEGIN
    SET NOCOUNT ON;
    ---- ********************************************************************************************************
    if (@name is not null)
    BEGIN
        update People
        set name = @name
        where Id = @ID
    END

    set @Counter = @@ROWCOUNT
END

What I want is here to count how many rows entered the if condition!

In SSIS -> I have a common variable, called RecordCounter = 0 by default.

I have a Data Flow Task in my Control Flow.

In the Data Flow: -> I have a Flat File Source, reading data from a .dat file.

Example data in the file:

ID|NAME
1  Jack
2  
3  Mike

Then, I have Derived Column, adding RecordCounter as a column to the data.

RecordCounter <add as new column> @[User::RecordCounter] four-byte signed integer [DT_I4]

Then, I have OLE DB Command, which enables the data goes row by row to my SP. SQL Command for that is:

EXEC [dbo].[ProcessClientRegistryRecord] ?,?,? OUTPUT

And finally, I have a Row Count item, mapping the variable User::RecordCounter

Here is the figure:

When I run this, the RecordCounter is showing the number of total data in my file, instead how many of them went into the IF block in the StoredProcedure. It should return 2 in this situation for example, not 3. Where is my mistake, how should I fix it? Any help would be appreciated!

In the SP? It is increasing one by one when a data goes to if condition? — Eray Balkanli
– Eray Balkanli, Commented May 30, 2016 at 21:59
Yes I can see this, but what value is passed to the procedure's @Counter variable and where does it come from? — M.Ali
– M.Ali, Commented May 30, 2016 at 22:05
In the Column Mappings section of OLE DB Command, RecordCounter is mapping to "@Counter". So I am expecting to have the value of RecordCounter SSIS variable as the value of "@Counter" — Eray Balkanli
– Eray Balkanli, Commented May 30, 2016 at 22:30
So correct me if I am wrong, you are not actually passing any value to the output parameter but expecting an output value, a number of rows affected by the update statement ? — M.Ali
– M.Ali, Commented May 30, 2016 at 22:34

M.Ali · Accepted Answer · 2016-05-31 00:00:16Z

0

After some explanation from you I think I know where you are going wrong.

I think you have done everything right except the logic in the stored procedure.

Your Stored procedure should look like....

CREATE PROCEDURE [dbo].[ExampleSP]
    @ID int,
    @name varchar(30),
    @Counter int output
AS
BEGIN
    SET NOCOUNT ON;

 IF (@name is not null)
  BEGIN

    update People
    set name = @name
    where Id = @ID

    SET @Counter = @@ROWCOUNT;  

  END
END

Finally in your SSIS package you can add an aggregate task to sum all the rows update from the derived column. You package should look something like...

In the OLE DB Transformation on the Component Properties tab should have the call to stored procedure as

Exec [dbo].[ExampleSP] ? , ? , ? OUTPUT

And finally on the Column Mapping tab, the OUTPUT parameter should be mapped to your derived column:

edited May 31, 2016 at 0:00

answered May 30, 2016 at 23:11

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

25 Comments

Eray Balkanli Over a year ago

The if condition is important here, I only need the total number of data entered inside the if condition. I would like to send RecordCounter variable as a parameter to the SP, and save the returned @Counter value as the new value of RecordCounter, then send the new value in my next loop (recursively)

Eray Balkanli Over a year ago

Edited a few things in my question for u to better understand.

M.Ali Over a year ago

@ErayBalkanli there is NO looping here. Your @Counter variable is always null, unless you pass a value to it at the time you are executing it. The update statement works with sets, it will update whatever the number of records it will find for the value passed in the ID variable and @@ROWCOUNT function will give you the number of records it will update.

Martin Smith Over a year ago

There's no need to aggregate. Just use SET @Counter += @@ROWCOUNT; to add to the previous value.

Eray Balkanli Over a year ago

OLE DB Command is using Exec [dbo].[ExampleSP] ? , ? , ? OUTPUT and the mapping is like exactly you have shown to me. I am trying to change SP now, will let u know the result.

|

Leo Bravo · Accepted Answer · 2019-11-19 05:47:18Z

0

Assigning value out of the context seems not to work at least you do a final assignment.

**Declare  @Id int =?, @rowCount int =?;
EXEC Exec [dbo].[ExampleSP]   @Id , @rowCount  OUTPUT;
SELECT ? = @rowCount;**

Also, be aware of mapping starts at 0.

answered Nov 19, 2019 at 5:47

Leo Bravo

313 bronze badges

Collectives™ on Stack Overflow

How to receive "output" variable of SP in SSIS to count how many data entered into conditional IF

2 Answers 2

25 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

25 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related