Store a Value in a Variable

Question

Store a Value in a Variable

sandeep.patel1

SSC-Addicted

Points: 497
More actions
March 27, 2014 at 1:48 pm

#285028

I have one value in SSIS Aggregate transformation (Avg. value). Let the value be 5.50 . I want to store this in a SSIS variable so that i can use it later in a different transformation.
I have no knowledge on Script task.Can i do this using Execute Sql Task.
So how can i do this. Kindly Suggest.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182920 More actions · Answer 1

Create a variable which has high enough scope for all the tasks you want to use the variable in. Then assign the initial value to the variable where the value is generated. Use where applicable.

How and where to do the initial assignment of the variable value depends on the package design, components used etc.. Let me know if you need further assistance.

sandeep.patel1 SSC-Addicted Points: 497 More actions · Answer 2

Hi Eirikur Eiriksson,

I want to assing my Aggregate transformation output(Which Returns only one value like 5.50) into a Variable.

How can i do this.

I already created a variable in Package Level scope.

Now i want to assign my transformation Value into this.

Phil Parkin SSC Guru Points: 246993 More actions · Answer 3

If you are writing the value to a table somewhere, you can issue an ExecuteSQL query immediately after the data flow to retrieve it and assign it to an SSIS variable.

Otherwise the only way I can think of doing this is as part of a script component. You want to run it only once, so do it as part of the pre- or post-execute sections in the script.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

sandeep.patel1 SSC-Addicted Points: 497 More actions · Answer 4

hi Phil Parkin,

Can you please write the script code for doing this.

I am unaware of this.

i have created a package level variable Avg_value having datatype Double.

I want to store my aggregate transormation value (5.50) to Avg_value Variable.

Thanks

Phil Parkin SSC Guru Points: 246993 More actions · Answer 5

sandeep.patel1 (3/28/2014)
hi Phil Parkin,
Can you please write the script code for doing this.
--

No.

Please do some research and at least try to make it work for yourself, then post back with any problems you have and someone will try to help.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

sandeep.patel1 SSC-Addicted Points: 497 More actions · Answer 6

Hi Phil,

I am trying to do this using Execute Sql Task.

But it shows below error:

[Execute SQL Task] Error: An error occurred while assigning a value to variable "Avg": "The type of the value being assigned to variable "User::Avg" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

".

My Cloumn data type is Float.

Variable data type is Double.

In Execute SQL Task: Parameter Mapping tab

Variable Name - @Avg

Direction - Output

Data Type - Double

Parameter name - 0

Parameter size - -1

Result Set - is set to single row

In Result set - Tab

Result Name -0

Variable Name - User::Avg

Eirikur Eiriksson SSC Guru Points: 182920 More actions · Answer 7

sandeep.patel1 (3/27/2014)
Hi Eirikur Eiriksson,
I want to assing my Aggregate transformation output(Which Returns only one value like 5.50) into a Variable.
How can i do this.
I already created a variable in Package Level scope.
Now i want to assign my transformation Value into this.

After the Aggregate, insert a Script Component (destination) and connect the two. Add the variable to the Read/Write variables property, select the input column and add the assignement in the script.

In the scrip, declare a variable i.e. RetVal

public class ScriptMain : UserComponent

{

Decimal RetVal = (-1);

.....

Then assign the value in ProcessInputRow...

public override void Input0_ProcessInputRow(Input0Buffer Row)

{

RetVal = Row.LineTotal;

}

In PostExecute, pass the value to the package variable.

public override void PostExecute()

{

base.PostExecute();

Variables.AggrVal = RetVal;

}

sandeep.patel1 SSC-Addicted Points: 497 More actions · Answer 8

Hi,

I did using Execute Sql task.

Sql qury data type float

SSIS variable data type - Double

aaron.reese SSChampion Points: 13498 More actions · Answer 9

I would data flow the value to a recordset destination to an object variable and then use a For-Each loop to extract it. As there will only ever be one 'row' in the object, the FEL will only iterate once. Its a bit ugly but it works and keeps the workflow fully within SSIS