SSIS Row Count For All Files Within a Folder

Question

I have a folder which contains multiple Excel files that are replaced daily. I need a total row count that gives me the sum of row counts from each individual Excel file within the folder (i.e. if there are 3 files with 10 records each, I need a result count of 30). I then need to run this package daily to add an individual record to a log table that will provide me with the daily count of records in the folder. I've been trying Foreach Loop Containers and ADO Enumerators but cannot seem to achieve a solution.

Answer 1

There is a good solution that you can apply without the need to use any script task. All you need to use is a "Foreach loop container" , a "Data Flow Task" and a "Execute SQL task"

Define Variables:

• V_FilesPath-> (String) Will hold the path where your files are located
• V_FileName-> (String) will be populated with the files name in the Foreach Container
• V_RowCount (Int)
• V_FileRowCount (int)
• V_TotalRecords (int)

Define the Foreach:

Map your Source file to a RowCount Component and select the Variable: V_FileRowCount

In the "Execute SQL task" change the result set to "Single Row"

Map the ResultSet to the following variables:

In the Expression part of the "Execute SQL task" Choose the following Property Expression:

In the Expression Pane issue the following:

" SELECT " + (DT_STR,10,1252)@[User::V_TotalRecords] + " + " + (DT_STR,10,1252) @[User::V_FileRowCount] + ", 1 + " +(DT_STR,10,1252) @[User::V_RowCount]

Once you've completed the aforementioned you're done!

If you wish to see the result add a Script task (Just to display the results)

and paste the following Script Code instead of the part that starts with "Public Void Main"

        public void Main()
{

    try
    {

        string Variables = "Loop Counter: " + Dts.Variables["User::V_RowCount"].Value.ToString() + "                       Total Records in all files: " + Dts.Variables["User::V_TotalRecords"].Value.ToString();
        MessageBox.Show(Variables).ToString();

    }



    catch (Exception Ex)
    {

        MessageBox.Show(Ex.Message);

    }
}