How to get the number of processed rows from a programmatically generated SSIS package to the calling application?

Question

I am programmatically generating and executing SSIS packages using C# and SQL Server 2012. The generated packages each contain one Data Flow Task with a Flat File Source for reading CSVs, and an OLE DB Destination connected to SQL Server. In between them is a Row Count component with a SSIS Variable hooked up.

After package execution ends, I want to get the value from the Row Count back to my calling application.

It seems that simply creating the variable & Row Count in code as follows:

[...]

// Row count: Create a package variable to store the row count value
var ssisRowCountVariable = package.Variables.Add("MySsisVar", false, "User", 0);

// Row count: Create Row component
IDTSComponentMetaData100 componentRowCount = dataFlowTask.ComponentMetaDataCollection.New();
componentRowCount.Name = "RowCount";
componentRowCount.ComponentClassID = "DTSTransform.RowCount.4";

// Row count: Get row count design-time instance, and initialize component
CManagedComponentWrapper instanceRowCount = componentRowCount.Instantiate();
instanceRowCount.ProvideComponentProperties();

// Row count: Set the variable name property
instanceRowCount.SetComponentProperty("VariableName", "User::MySsisVar");

// Hooking up pipeline Paths
[...]

// Execute package
package.Execute()

and then trying to read the value after package execution:

int Rows = Convert.ToInt32(ssisRowCountVariable.Value);

does not work.

How can I get the value of the Row Count component back to the calling application?

Answer 1

After playing with the second approach some more, I found that the best way to do this seems to be to hook up the Row Count component as described in the question, and add code to let it fire an event when its value gets set or changes:

ssisRowCountVariable.RaiseChangeEvent = true;

Now set up an event handler that derives from the standard DefaultEvents class to capture the OnVariableValueChanged event:

class MySsisEvents : DefaultEvents
{
    public int Rows { get; private set; }

    public override void OnVariableValueChanged(DtsContainer DtsContainer, Variable variable, ref bool fireAgain)
    {
        this.Rows = Convert.ToInt32(variable.Value);
    }
}

The call to package.Execute() has to be modified to hook up the event handler as follows:

// Execute package
var mySsisEventHandler = new MySsisEvents();
package.Execute(null, null, mySsisEventHandler, null, null);

The number of processed rows is now available as mySsisEventHandler.Rows.

Answer 2

Unfortunately, you cannot get runtime values of Package variables from package.Execute() call directly. However, your task of getting # of processed records can be achieved differently with one of the following ways:

• Add a task to the Package which saves value from processed rows variable to some storage. Task can be either following DataFlow or in its PostExecute handler.
  Storage can be SQL Database or something else like Web service; value will be saved with either Execute SQL Task for SQL DB or Script Task/Web Service Task for Web service. Here is a good review of this approach.
• Use DataFlow standard PostExecute informational message [SSIS.Pipeline] Information: "<your destination name>" wrote N rows. Execute your package capturing events as described in MSDN, and then check results.

Please mind that the second approach only will work if you run package as package.Execute in SSIS without using SSIS Catalog. If you are generating packages yourself and executing it right after generation - its ok, you are not generating and deploying SSISDB Projects.