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?

Here you go here is the problem, your stored procedure doesnt have the right logic, your @Howmany variable is always null unless you pass a value to it at run-time, since the only values you have is the ID and Name your Howmany variable will always be null in this case. And where is your UPDATE statement in the procedure ? — M.Ali
– M.Ali, Commented May 31, 2016 at 14:30
why HowMany always null? since I write output keyword in its definition? If I remove OUTPUT keyword there, how can I return it? — Emel Uras
– Emel Uras, Commented May 31, 2016 at 14:32

Tab Alleman · Accepted Answer · 2016-05-31 14:58:47Z

6

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;

edited May 31, 2016 at 14:58

answered May 31, 2016 at 13:58

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

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

4 Comments

Emel Uras Over a year ago

Yes I was doing it. However, it is not working. Default value of "@howMany" is 0. I want to increase this value dynamically when NAME value is not coming null from the Flat File. When I do it like you said, "@howMany" goes as 0 to all the data.

Emel Uras Over a year ago

I am checking. Will let u know. Thanks

Emel Uras Over a year ago

This is good. I feel I am very close. Do you have time to chat a little on it?

Emel Uras Over a year ago

In the data context, Dts is not available namespace. Instead, Variables.<VariableName> is available. I made it like you said, seems like working as I expected now! Thanks a lot!

M.Ali · Accepted Answer · 2016-05-31 14:34:36Z

0

Right since you have mentioned that the @howMany variable is an output variable you would need to use the OUTPUT clause in your OLE DB Command tranformation.

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

And as Tab Alleman has suggested add a derived column before the OLE DB Command tranformation and in the Column Mapping tab of the OLE DB Command transformation , map it to the derived column.

Now you have mentioned that you would also want a total sum of the rows updated.

You can add an Aggregate transformation after the OLE DB Command and Sum on the Derived column with no Grouping and this will give you the total number of rows updated.

Update

Here you go here is the problem, your stored procedure doesnt have the right logic, your @Howmany variable is always null unless you pass a value to it at run-time, since the only values you have is the ID and Name your Howmany variable will always be null in this case.

Your Stored procedure logic is wrong and it should be something like....

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

   Update TableName --<-- whatever your table name is 
    SET Name = @name 
   WHERE ID = @ID 
    AND  Name IS NOT NULL     

set @howMany = @@ROWCOUNT;

END

edited May 31, 2016 at 14:34

answered May 31, 2016 at 14:20

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

3 Comments

Emel Uras Over a year ago

updated the question, explained why I could not use Derived Column.

M.Ali Over a year ago

@EmelUras can you show the Stored procedure definition ?

Emel Uras Over a year ago

Added to the question.

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related