為什麼 SSIS 列舉目錄中的許多文件並導入它們很慢？

February 24, 2022

我有一個非常慢的 SSIS 包。一個文件的速度相當快，而 100 個或更少的文件則相當快。（每個文件大約一秒鐘）
但是，如果我的目錄有數千個（非常小的）文件，則該過程會拖得很慢。我的偏好是僅在工作時間之後執行此過程，但是等到那時，要導入的平面文件的數量是數千個。
包非常簡單：
外循環是 For Every（文件列舉，將文件路徑讀入變數）
在內部，只需導入，無需對數據進行任何轉換
這就對了。
數千個文件的性能每個文件執行 15 秒或更長時間。UI（狀態）的繪製/滾動速度非常慢，以至於我什至看不到它的位置——在 18 小時前開始的執行上，加蓋時間超過 15 小時。
版本：MSSQL 2012

我認為您遇到了 UI/調試器的限制。
我創建了兩個包：MakeAllTheFiles 和 ReadAllTheFiles
MakeAllTheFiles 接受要創建的文件數作為輸入。它將利用偽隨機函式將數據分佈在數（7）個子文件夾中。
製作所有文件
   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 &lt; 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 &lt; 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;
   }
閱讀所有文件
包裝的一般外觀是這樣的
我定義了兩個連接管理器：一個是我的數據庫，另一個是一個平面文件，在 ConnectionString 屬性上有一個表達式，以便它使用我的變數@[User::CurrentFileName]
變數，我喜歡很多變數，所以有很多。
我的執行 SQL 任務只是為我建立一個表來寫入，如果它已經存在則將其擊倒。
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)
);
我的 Foreach 列舉器只是根據 *.txt 的文件遮罩查看我的 Input 文件夾中的所有內容並遍歷子文件夾。目前文件名分配給我的變數@
$$ User::CurrentFileName $$`
數據流是沼澤標準。那裡的派生列轉換只是將目前文件名變數添加到數據流中，以便我可以將它記錄在我的表中。
分析
我很懶，不想做任何特別的事情來記錄處理時間，所以我將我的包部署到 SSISDB 目錄中並從那裡執行它們。
此查詢查看目錄數據以了解程序包執行了多長時間、處理了多少文件，然後生成文件計數的執行平均值。執行 10047 是錯誤的，被排除在分析之外。
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 &lt;&gt; 10047
ORDER BY 1,2
結果數據（免費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
基於此採樣大小，我發現使用此處描述的 SSIS 處理 100、1000 或 10,000 個文件之間沒有明顯差異。
根本原因假設
根據有關該評論的評論DTExecUI.exe，您正在從 Visual Studio (BIDS/SSDT/name-of-the-week) 中執行該包。為了獲得漂亮的顏色變化和調試功能，本機執行 (dtexec.exe) 包含在調試過程中。這對執行造成了明顯的拖累。
使用設計環境創建包並針對較小的數據集執行它們。較大的最好通過非圖形和非調試器執行界面處理（VS 中的 shift-F5，部署到 SSIS 目錄並從那裡執行，或 shell 到命令行界面並使用 dtutil.exe）