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, May 30 '16 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, May 30 '16 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, May 30 '16 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, May 30 '16 at 22:34
You might be right. But I dont know how to fix this situation. Can you help me on this? — Eray Balkanli, May 30 '16 at 22:43
EXEC [dbo].[ProcessClientRegistryRecord] ?,?,? OUTPUT Here the last ? is supposed to belong to RecordCounter variables? — Eray Balkanli, May 30 '16 at 22:44

M.Ali · Answer 1 · 2016-05-31T00:00:16.917

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 '16 at 00:00

answered May 30 '16 at 23:11

M.Ali

67,945
13
101
127

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 May 30 '16 at 23:18
Edited a few things in my question for u to better understand. – Eray Balkanli May 30 '16 at 23:19
@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. – M.Ali May 30 '16 at 23:29
There's no need to aggregate. Just use `SET @Counter += @@ROWCOUNT;` to add to the previous value. – Martin Smith May 30 '16 at 23:42
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. – Eray Balkanli May 30 '16 at 23:44
@MartinSmith the procedure is being called for each row. On next call to the procedure `@Counter` will be null. – M.Ali May 30 '16 at 23:44
I dont understand why you removed IF condition in the SP but? I need to check if @name is null or not? – Eray Balkanli May 30 '16 at 23:46
@ErayBalkanli yes you can add the if statement to check other stuff but not the `@Counter` variable, it will always be null in this case. – M.Ali May 30 '16 at 23:47
how come @@RowCount; can return me 2 in the end? There will be 3 records will come to SP from the file read by SSIS? – Eray Balkanli May 30 '16 at 23:49
Ah, I see, I thought it would work like the execute SQL task and assign the output param back into an ssis variable but doesn't look like it works that way... – Martin Smith May 30 '16 at 23:50
Do you already have all three IDs in the table? mind you this procedure only updates record so only the ID that already exists in the table will be updated. – M.Ali May 30 '16 at 23:50
Yes I do have all IDs in the table. Just the ones who comes with `not null` name value from SSIS should be updated only. And those updates would be counted – Eray Balkanli May 30 '16 at 23:52
@MartinSmith this is `OLE DB Command` transformation runs inside a data flow task i.e once for each row , unlike `Execute SQL` task which is a control flow task runs once unless used inside a foeach loop. – M.Ali May 30 '16 at 23:57
Should I select only name in the "group by" in SSIS, right? – Eray Balkanli May 30 '16 at 23:59
Yes, I know. But if it assigned the value of the output param back into the SSIS variable (which it doesn't so hypothetical anyway) then on the next call for the next row the accumulated value so far would be passed in to the output parameter and added to. – Martin Smith May 31 '16 at 00:00
When I select only name, it is returning 2, so it is true. But wanna make sure something that: Is it counting "JACK" and "MIKE" and not counting the null value in the groupby then, right? – Eray Balkanli May 31 '16 at 00:01
@ErayBalkanli `Group By` for Aggregate ? if so , you dont need to group by anything, if you just want to get the total number of rows updated, Also do you know something called `Data Viewer`? enable that and you can see the actual data flowing throwing pipes. – M.Ali May 31 '16 at 00:04
Aggregate pushes me select one attribute to count and one attribute (at least) to group by. It doesnt let me just pull and put an Aggregate, connect it to the figure and run the application. (I dont know Data Viewer) – Eray Balkanli May 31 '16 at 00:09
Sorry, it is possible not to select any attribute as group by, but in this case RecordCounter is returning 1, not 2 – Eray Balkanli May 31 '16 at 00:11
I believe aggregate is not specifically counting how many records went into IF condition. It seems like it is grouping the data by name and returning total number of groups. – Eray Balkanli May 31 '16 at 00:14
In the `aggregate` transformation, just select the `Derived column` as your `INPUT column`, The column you mapped to OUTPUT parameter in the OLE DB transformation, In `operation` select `SUM` and leave the group by empty. – M.Ali May 31 '16 at 00:17
The SP is exactly what you provided now. Aggregate has only one attribute, the one I derived to map to output parameter and operation is SUM. Still returning 1 instead 2. :( – Eray Balkanli May 31 '16 at 00:36
Just a note, making set @Counter += @@ROWCOUNT doesnt change the result, still 1. – Eray Balkanli May 31 '16 at 00:38
I believe the counter value is not getting updated after each loop, the SSIS variable RecordCounter should be updated after each data going into the IF. Still it is not provided somehow here, right? :( – Eray Balkanli May 31 '16 at 00:43
The default value of RecordCounter SSIS variable is 0, so 0 is going to the SP for each data. We should manage to update it. It is linked to @Counter int OUTPUT variable in SSIS. Why it is not getting updated you think? – Eray Balkanli May 31 '16 at 00:45

score 0 · Answer 2 · answered Nov 19 '19 at 05:47

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 '19 at 05:47

Leo Bravo

11
3

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

2 Answers2