+ Reply to Thread
Results 1 to 3 of 3

Logging record counts in SSIS

  1. 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


  2. RE: Logging record counts in SSIS

    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
    >


  3. RE: Logging record counts in SSIS



    "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.

+ Reply to Thread