RE: Logging record counts in SSIS

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance



"Todd C" wrote:

Hello:

May have something to do witht he fact that you are using Event Handlers
instead of Control Flow.
Just out of curiosity, why are you using Post-Exec event handlers and not
putting your Execute SQL right inside the Control Flow?

From what I understand, if you have, say 20 files and each processes about
100 records, then you want to, after all is said and done, record the fact
that an aggregate of 2,000 rows were processed. Is this correct?

If so, I would do this: Create another Package scoped variable called
RowCountAggregate.
Then add a Script Task inside the For Each loop, and have it execute after
the Data Flow. Set the Read Only Variables to RowCount and the ReadWrite
Variables to RowCountAggregate.
Inside the Script itself, have one line:

Dts.Variables("RowCountAggregate").Value =
Cint(Dts.Variables("RowCOuntAggregate").Value +
Cint(Dts.Variables("RowCount").Value

This will make the RowCountAggregate a 'running sum' of the rows processed.

Now, AFTER the loop, add an Execute SQL task the fires the insert statement,
with a mapping of this variable to one of the SP input parameters.

Keep us posted.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"GPage" wrote:

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


Thanks Todd your suggestion worked much better. I was doing it the way I was
because I was cribbing off of another post on the net, but the aggregator
script allows for much more control.
.

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)
  • Logging record counts in SSIS
    ... between the file source and database target in the data transfer task. ... assigned the RowCount variable to the VariableName property of the Row Count ... package level and the Data Transfer Task level which runs an execute sql task ...
    (microsoft.public.sqlserver.dts)