Search code examples
variablesloggingssis

SSIS Row Count Variable Value Send To Log

I have a SSIS 2019 package that has a flat file for data source and SQL Server for destination. Coming out of the flat file source is a Row Count task to get the number of rows saved in variable. I would like to capture that variable value in the SSIS catalog report log. Meaning, go to the SSIS Catalog, go to project, select package, right click for Report, and view the log.Thanks.

Solution

  • Create a variable, @[User::RowCount] of type Int32 (or Int64 based on your needs). Initialize to zero, or -1 or whatever your requirements specify.

    Add a Data Flow task to your SSIS Package. Pick a source, any source. Add a Row Count transformation after it and select your @[User::RowCount] variable.

    You can deploy the project to the SSISDB and run that package with any of the logging levels: Basic, Performance, Verbose, None and nowhere can I find a place where SSIS will tell you the ending value of variables. Parameter(s) starting values, yup, right there in the reports.

    But, you can explicitly ask SSIS to log it and that's a trivial 5 line Script Task that you have wired up after the Data Flow task.

    Select your variable @[User::RowCount] in the ReadOnly variables drop down and in the resulting editor, add these lines to Main

    bool fireAgain = false;
string message = "{0}::{1} : {2}";
foreach (var item in Dts.Variables)
{
    Dts.Events.FireInformation(0, "SCR Echo Back", string.Format(message, item.Namespace, item.Name, item.Value), string.Empty, 0, ref fireAgain);
}

    Run the package using the default Basic logging and then click the Messages link in the Overview report. Somewhere you'll have an OnInformation event fired and there's my value of 33 from the variable that the Row Count transformation used.

    enter image description here

    Now, that's fine for a one-stop shop for logging but I'd really encourage you to take a different approach, like a custom table and an Execute SQL Task to populate it. Otherwise, you'll have to parse through the ssisdb.catalog.operation_messages table every time and then parsing string values to extract the value of 33 (or whatever).