+ Reply to Thread
Results 1 to 2 of 2

Using SSIS packages to get Recordcount of all tables

  1. Using SSIS packages to get Recordcount of all tables

    Hi,

    I am new to SSIS. I need to find a way to get the record count for each table
    in a database using SSIS packages. I cannot use the count from sysindexes. I
    have to do a manual count. I have to repeat this process for 3 other
    databases that have the same table names. I then have to write the results to
    a flat file in the following format.

    TableName DB1 DB2 DB3 DB4
    Table1 250 120 230 450
    Table2 123 234 256 678

    etc.

    Can anyone provide a general overview of what needs to be done to make this
    happen? I am thinking of a foreach loop but I am not clear on how to assign
    and retrieve variables and there is not much documentation I could find.

    Thanks in advance for your help


  2. RE: Using SSIS packages to get Recordcount of all tables

    "SumTotal" wrote:

    > Hi,
    >
    > I am new to SSIS. I need to find a way to get the record count for each table
    > in a database using SSIS packages. I cannot use the count from sysindexes. I
    > have to do a manual count. I have to repeat this process for 3 other
    > databases that have the same table names. I then have to write the results to
    > a flat file in the following format.
    >
    > TableName DB1 DB2 DB3 DB4
    > Table1 250 120 230 450
    > Table2 123 234 256 678
    >
    > etc.
    >
    > Can anyone provide a general overview of what needs to be done to make this
    > happen? I am thinking of a foreach loop but I am not clear on how to assign
    > and retrieve variables and there is not much documentation I could find.
    >
    > Thanks in advance for your help
    >
    >


    This is a case for NESTED for-each loops, the outer loop being the
    Database(s), and the inner loop being the table(s). I would start with just
    the inner loop for now, and add the outer when this phase is complete.

    Try an ADO Schema rowset enumerator, and select Tables. Under Variable
    Mappings page, create a new mapping to a variable called TableName.
    Create another variable called SQLtext. Create a Script task inside the loop
    that builds the SQL command as "SELECT COUNT(*) FROM " +
    Cstr(Dts.Variables("TableName").Value).
    Create an Execute SQL task downstream of the Script task that executes the
    SQLtext variable. Have the result set mapped to an integer variable RowCount.

    To record the results back to a table, you will probably need a stored
    procedure where you can pass it the TableName and RowCount (and eventually
    the DbName) variables and it should be designed to either update a row or
    insert a new one accordingly. The last task inside the loop would be another
    Execute SQL that passes the SSIS variables to the stored procedure variables.

    Keep us posted and good luck
    --
    Todd C

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


+ Reply to Thread