Capture aggregate value from data flow task into a variable

bids, sql-server, ssis

Solution

One way of doing it would be to add a multicast transform between the source and destination that also feeds into a script component.

Whilst an aggregate transform would also work this method avoids adding a blocking transform

Configure the Script Component as a destination, give it read/write access to the variable and then edit the script to be something 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;
    }
}

Problem

I have an OLEDB (SQL) data flow source (A) that pulls a result set from a stored procedure and throws the results into an OLEDB (Oracle) data flow destination (B). Is there a way to capture an aggregate value from the dataset into a variable, all within the data flow task? Specifically, I'd want to capture the `MAX(<DateValue>)` from the entire dataset. Otherwise, I'd have to pull the same data twice in a different data flow task, whether I point to A or in its new location, B. EDIT: I already know how to do this in the Control Flow from an Execute SQL task. I'm asking because I'm curious to know if I can get this done in the Data Flow task since I'm already collecting the data there. Is there a way to grab an aggregate value in the Data Flow?

Original source