Passing an SSIS variable value as a parameter of SP via OLE DB Command

Question

I would like to ask how can I pass an SSIS variable as a parameter of the used Stored Procedure by OLE DB Command in Data Flow?

I am trying to write the following as SQL Command, but not working: EXEC [dbo].[CalculateProcess] ?,?,?

Received error:

No value given for one or more required parameters.

The first and second values (? values) are coming from Flat File Source as ID, and NAME. The third one should be the value of SSIS variable @howMany. How can I do it? Thanks for help.

EDIT:

Lets assume my dataset is:

ID NAME
1  X
2  Y
3  Z
4  
5  A

In this case, @howMany should be 4 at the end. The value should be updated dynamically after each OLE DB Command works if current NAME is not null.

ID NAME
1  X
@howMany = 1

2  Y
@howMany = 2

3  Z
@howMany = 3

4  
@howMany = 3

5  A
@howMany = 4

EDIT 2: When I do add @howMany as derived column, it is happening like:

ID NAME @howMany
1  X    0
2  Y    0 
3  Z    0
4       0
5  A    0

And the 0 values are staying, not getting updated after each row. writing OUTPUT after ? in the SQL command did not solve it? Where is my mistake?

EDIT 3: Stored Procedure is --

CREATE PROCEDURE [dbo].[CalculateProcess]
    @ID int,
    @name varchar(30),
    @howMany int output
AS
BEGIN
    SET NOCOUNT ON;
    if (@name is not null)
    BEGIN
        set @howMany = @howMany + 1;
    END
END

EDIT 4: I added a Script Component right after getting Flat File Source. Name is the INPUT parameter, NameCounter is my output column. My script is:

private int nameCounter;
public override void PreExecute()
{
    nameCounter= 0;
}
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    if (Row.Name != null)
    {
        nameCounter++;

    }
    Row.NameCounter= nameCounter;
}

The Script Component is connected to my OLE DB Command. This is generating a column called NameCounter and adding it to all data in the OLE DB Command. Now I just dont know how to get the value of NameCounter here and assign it to an SSIS variable?

Answer 1

Use a Derived Column transformation before the OLE DB Command, and add the variable to your dataflow as a new column. Then use the new column as the third parameter in your OLE DB Command.

EDIT based on updated question:

Ok, to do what you want you can use a script component. Here is an example. Increase the value of the howMany variable in the script if the NAME column is not null, and then add the value of the variable as a third column.

2nd Edit:

To update the value of an SSIS variable in a script, be sure to set the variable as a Read/Write variable in the script properties, unlike in the article I linked above. Then:

Dts.Variables["howMany"].Value = nameCounter;