Inside this, we have a OLE DB Data Source which calls and executes a stored procedure.
We use a Row Count Task to count the number of rows returned by the OLE DB Data Dource Task.
Then, we use an aggregate function task to get the sum of all the rows of one particular column that is returned from the OLE DB Data Source.
The issue here is that we want to assign the sum value returned by the Aggregate Function Task to a User Variable named User::Variable. We have tried to assign this by using @.User::Variable and User::Variable, but neither of those return the expected value.
If there is any other method to do the same then let us know.
We have checked the that the row count is greater than zero.
Any help would be very much appreciated.
Thanks in advance.Yep, you can't store that value in a variable.... Your best bet would be to use a script component to capture that result and store it.|||How can we access the output of the aggregate function in script task?
Or Is it possible to use script task after a Data source task (without using aggregate)?
Is there any other method to get the sum of a field of data source task in a variable? I think it can be done using Expressions but when i have treed i was not able to get expression in data flow task.|||Use a script component in transformation mode...
Notice how Jamie uses the row.[fieldname] convention. You'll have an output coming from your aggregate transformation, which you can use in the script component. Search this forum for many examples on working with variables.
http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-The-script-component-and-regular-expressions.aspx
No comments:
Post a Comment