SQL Server – How to Read Data from Multiple Excel Files with SSIS

sql serverssdtssisvisual studio

Hope you're doing well.
I have a scenario here.
As you can see I have a table with two main columns :

What I need is to read each "FilePath" which is "Active" right now and then read the Excel files in that path. So I assume that I need at least 2
"For Each loop Container" inside each other. I've already done some part of the scenario :

Within the "Execute sql task" I'm retrieving "FilePath" which are active from the table and putting them in an "object" variable (My first variable in SSIS) .

Then I have my second variable in SSIS in which I keep the name of each "FilePath" each time the outer loop is being executed.

At the end I have my third variable which will change each time the inner loop in being executed and it carries the Excel files.

The outer loop must execute to the number of active rows in meta data table and the inner loop must execute to the number of file within the path.

The point is I receive no error after executing the package, but the data won't be inserted in my "OLEDB Destination"
I was wondering if you could help me with this issue.

Best Answer

When you initially pull the file paths from the ExcelFilePath table, trying returning just names of the folders holding the Excel files that will be used and make sure these end with a \ (i.e. C:\Excel_Exercise\Excel_Source\). You may need to used a T-SQL function like RIGHT/SUBSTRING, but doing this will allow you to load the files using the method outlined below. Also, are you filtering for only "Active" file paths in the SQL query from the Execute SQL Task? As you're probably already doing, make sure to set the DelayValidation and ValidateExternalMetadata properties accordingly.

The First Foreach Loop will be a Foreach ADO Enumerator using the object variable from the Execute SQL Task as the source variable. In the Variable Mappings pane, add a string variable that will hold the file directory at index 0.
Inside the first Foreach Loop, add another Foreach Loop as a Foreach File Enumerator. On the Collection pane, add an expression for the Directory property that uses the string variable from the outer Foreach loop with the directory. On this Foreach Loop, add a variable in the Variable Mappings pane the will be used to hold the full file path (including file name/extension).
Within this Foreach Loop add a Data Flow Task that loads from an Excel Source to the desired destination. On the Connection Manager for the Excel Source, create an expression for the ExcelFilePath and add the variable holding the complete file path and name/extension.

MakeAllTheFiles

    public void Main()
    {
        int NumberOfFilesToGenerate = (Int32)Dts.Variables["User::FilesToGenerate"].Value;
        string baseFolder = Dts.Variables["User::FolderInput"].Value.ToString();
        System.Random rand = null;
        int fileRows = 0;
        DateTime current = DateTime.Now;
        int currentRandom = -1;
        int seed = 0;
        string folder = string.Empty;
        string currentFile = string.Empty;

        for (int i = 0; i < NumberOfFilesToGenerate; i++)
        {
            seed = i * current.Month * current.Day * current.Hour * current.Minute * current.Second;
            rand = new Random(seed);
            currentRandom = rand.Next();

            // Create files in sub folders
            folder = System.IO.Path.Combine(baseFolder, string.Format("f_{0}", currentRandom % 7));

            // Create the folder if it does not exist
            if (!System.IO.Directory.Exists(folder))
            {
                System.IO.Directory.CreateDirectory(folder);
            }

            currentFile = System.IO.Path.Combine(folder, string.Format("input_{0}.txt", currentRandom));

            System.IO.FileInfo f = new FileInfo(currentFile);
            using (System.IO.StreamWriter writer = f.CreateText())
            {
                int upperBound = rand.Next(50);
                for (int row = 0; row < upperBound; row++)
                {
                    if (row == 0)
                    {
                        writer.WriteLine(string.Format("{0}|{1}", "Col1", "Col2"));                        }

                    writer.WriteLine(string.Format("{0}|{1}", row, seed));
                }
            }
            ;
        }
        Dts.TaskResult = (int)ScriptResults.Success;
    }

ReadAllTheFiles

The general appearance of the package is thus

Read All The Files!

I have two Connection Managers defined: One is to my database and the other is to a Flat File with an Expression on the ConnectionString property such that it uses my Variable @[User::CurrentFileName]

Variables, I like lots of Variables so there are plenty.

enter image description here

My Execute SQL Task simply stands up a table for me to write to, knocking it down if it already exists.

IF EXISTS
(
    SELECT * FROM sys.tables AS T WHERE T.name = 'dbase_54462' AND T.schema_id = SCHEMA_ID('dbo')
)
BEGIN
    DROP TABLE dbo.dbase_54462;
END

CREATE TABLE
    dbo.dbase_54462
(
    CurrentFile varchar(256) NOT NULL
,   Col1 int NOT NULL
,   Col2 varchar(50) NOT NULL
,   InsertDate datetime NOT NULL DEFAULT(CURRENT_TIMESTAMP)
);

My Foreach Enumerator simply looks at everything in my Input folder based on the file mask of *.txt and traverses subfolders. The current file name is assigned to my variable @[User::CurrentFileName]`

enter image description here

The Data Flow is bog standard. The Derived Column Transformation there simply adds in the Current File Name variable into the data flow so I can record it in my table.

enter image description here

Analysis

I'm lazy and didn't want to do anything special to record processing times so I deployed my packages into the SSISDB catalog and ran them from there.

This query looks at the catalog data to find out how long the package ran, how many files it processed and then generates a running average for file count. Run 10047 was bad and was excluded from analysis.

SELECT
    E.execution_id
,   DATEDIFF(s, E.start_time, E.end_time) As duration_s
,   ES.rc AS FilesProcessed
,   AVG(ES.rc / (1.0 * DATEDIFF(s, E.start_time, E.end_time))) OVER (PARTITION BY ES.rc ORDER BY E.execution_id) AS running_average
FROM
    catalog.executions As E
    INNER JOIN
    (
        SELECT
            MIN(ES.start_time) As start_time
        ,   MAX(ES.end_time) AS end_time
        ,   count(1) As rc
        ,   ES.execution_id
        FROm
            catalog.executable_statistics AS ES
        GROUP BY
            ES.execution_id
    ) AS ES 
    ON ES.execution_id = E.execution_id
WHERE
    E.package_name = 'ReadAllTheFiles.dtsx'
    AND E.execution_id <> 10047
ORDER BY 1,2

The resulting data (gratuitous SQLFiddle)

execution_id    duration_s  FilesProcessed  running_average
10043   15  104 6.93333333333333
10044   13  104 7.46666666666666
10045   13  104 7.64444444444444
10050   102 1004    9.84313725490196
10051   101 1004    9.89186565715395
10052   102 1004    9.87562285640328
10053   106 1004    9.77464167060435
10055   1103    10004   9.06980961015412
10056   1065    10004   9.23161842010053
10057   1033    10004   9.38255038913446
10058   957 10004   9.65028792246735
10059   945 10004   9.83747901522255

Based on this sampling size, I see no appreciable difference between processing 100, 1000 or 10,000 files with SSIS as described herein.

Root cause assumption

Based on the comment about DTExecUI.exe that says you're running the package from within Visual Studio (BIDS/SSDT/name-of-the-week). To get the pretty color changes and debugging capability, the native execution (dtexec.exe) is wrapped up in the debugging process. That creates an appreciable drag on execution.

Use the design environment to create your packages and to run them for smaller data sets. Larger ones are best handled through the non-graphical & non-debugger execution interfaces (shift-F5 in VS, deploy to SSIS catalog and execution from there, or shell to the command line interface and use dtutil.exe)

Best Answer

Related Solutions

Sql-server – SSIS hangs during For Each File enumeration

Sql-server – Why is SSIS slow to enumerate over many files in a directory and import them

MakeAllTheFiles

ReadAllTheFiles

Analysis

Root cause assumption

Related Question