Logging record counts in SSIS

Hello,
I have an SSIS package that loops through files in a folder using a for
each file loop control object. It then runs a data flow task for each file to
load the flat file into table. I want to get an aggregate count of how many
rows total were added to the table and put that in an audit table.
I have created a variable RowCount and put a Row Count transformation in
between the file source and database target in the data transfer task. I have
assigned the RowCount variable to the VariableName property of the Row Count
Transformation. I have also added a post execute event handler at both the
package level and the Data Transfer Task level which runs an execute sql task
that uses an expression to create the insert statement to the audit table.
The expression casts the RowCount variable as string and adds it to the
insert statement.
When I run this package it runs successfully, and several rows are added to
the audit table, I assume several rows because postexecute at the package
level runs once per task completion? The real problem though is that all the
row counts in the audit table show 0 which is what the variable is
initialized to, so it looks like the RowCount transformation is either not
working, or the variable is being re-initialized somewhere. The variable is
scoped at the package level.
I'd like some help in figuring out why the variable is not storing the
rowcounts like it should.

Thanks

.

Relevant Pages

  • RE: Logging record counts in SSIS
    ... putting your Execute SQL right inside the Control Flow? ... I would do this: Create another Package scoped variable called ... Set the Read Only Variables to RowCount and the ReadWrite ... between the file source and database target in the data transfer task. ...
    (microsoft.public.sqlserver.dts)
  • RE: Logging record counts in SSIS
    ... putting your Execute SQL right inside the Control Flow? ... I would do this: Create another Package scoped variable called ... Set the Read Only Variables to RowCount and the ReadWrite ... between the file source and database target in the data transfer task. ...
    (microsoft.public.sqlserver.dts)