Capturing the cumulative value from a data flow task to a variable

Question

I have an OLEDB (SQL) data stream source that pulls the result set from a stored procedure and outputs the results to the OLEDB (Oracle) data stream destination.

Is there a way to capture the aggregate value from a data set into a variable, all within the scope of a data flow task? In particular, I would like to capture MAX(<DateValue>)from the entire data set.

Otherwise, I would have to pull the same data twice in another data flow task, regardless of whether I point to A or in a new location, B.

EDIT: I already know how to do this in a control flow from an Execute SQL task. I ask because I am interested in knowing if I can do this in a data flow task, since I already collect data there. Is there a way to capture the aggregate value in a data stream?

Answer 1

One way to do this is to add a multicast conversion between the source and the receiver, which will also be passed to the script component.

While the aggregate transform will also work, this method avoids adding a blocking transform

Set up the script component as the destination, give it read / write access to the variable, and then edit the script like

//Instance level variable
DateTime? maxDate = null;

public override void PostExecute()
{
    base.PostExecute();

    if (maxDate.HasValue)
    {
        this.Variables.MaxDate = maxDate.Value;
    }

    System.Windows.Forms.MessageBox.Show(this.Variables.MaxDate.ToString());
}


public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    if (!Row.createdate_IsNull)
    {
        maxDate = Row.createdate < maxDate ? maxDate : Row.createdate;
    }
}

Answer 2

• U DFT ( )
• Exceute SQL, MAX(), .

Eg: 
--Let the given be Your source query.
SELECT  ColumnA,
        ColumnB,
        ColumnC,
        DateValue
FROM    SourceA

--Your new query to calculate MAX() may be this.
SELECT  MAX(DateValue)
FROM    SourceA

SQL SQL. int . (: name = intMax)

In the Execute SQL task, not the following.
    a.general Tab
        Result Set  = Single Row
        Sql Statement = SELECT  MAX(DateValue) FROM SourceA
    b.result set Tab
        click ADD
        ResultName = 0
        variable Name = variable Name (eg: name = intMax)

.