SSIS需求：从指定共享文件夹获取最大文件名的CSV文件导入SQL

Got it, let's work through this SSIS scenario step by step. Since we can't use For Each loops and need to avoid temp tables for filename handling, we'll rely on SSIS variables, expressions, and a Script Task to grab the largest CSV file directly from your network share.

We'll break this into 5 core steps:

1. Create variables to store the folder path, file pattern, target filename, and full file path
2. Use a Script Task to scan the folder, sort files by their numeric suffix, and capture the largest filename
3. Build the full path to the target file using an SSIS expression
4. Link the Flat File Connection Manager to our dynamic file path variable
5. Configure the Data Flow to import the CSV into a global temp table (##Temp)

First, set up these user variables in your SSIS package (go to the Variables pane, right-click -> Add Variable):

• @FolderPath (String): Set the value to \\Share\Folder\ (your target network path)
• @FilePattern (String): Use ABC_DE_FGHIJKL_MNO_PQRST_U-*.csv to match your specific filename pattern (or *.csv if you want all CSVs, but the targeted pattern is safer)
• @MaxFileName (String): Leave this empty initially; we'll populate it via the Script Task
• @FullFilePath (String): We'll set an expression on this variable later

Add a Script Task to your Control Flow. Configure it as follows:

1. In the Script Task Editor, go to the Script tab
2. Set ReadOnlyVariables to User::FolderPath, User::FilePattern
3. Set ReadWriteVariables to User::MaxFileName
4. Click Edit Script to open the code editor (we'll use C# here)

Replace the default Main method with this code—it extracts the numeric suffix from each filename, sorts by that number, and grabs the largest one:

using System;
using System.IO;
using Microsoft.SqlServer.Dts.Runtime;

public class ScriptMain
{
    public void Main()
    {
        string folderPath = Dts.Variables["User::FolderPath"].Value.ToString();
        string filePattern = Dts.Variables["User::FilePattern"].Value.ToString();
        
        // Get all CSV files matching the pattern
        string[] matchingFiles = Directory.GetFiles(folderPath, filePattern);
        
        if (matchingFiles.Length == 0)
        {
            // Raise an error if no files are found
            Dts.Events.FireError(0, "Fetch Largest File", "No matching CSV files found in " + folderPath, string.Empty, 0);
            Dts.TaskResult = (int)DTSExecResult.Failure;
            return;
        }
        
        // Sort files by their numeric suffix (the part after the final '-')
        Array.Sort(matchingFiles, (fileA, fileB) =>
        {
            // Extract the numeric portion from each filename
            string fileNameA = Path.GetFileNameWithoutExtension(fileA);
            int numA = int.Parse(fileNameA.Substring(fileNameA.LastIndexOf('-') + 1));
            
            string fileNameB = Path.GetFileNameWithoutExtension(fileB);
            int numB = int.Parse(fileNameB.Substring(fileNameB.LastIndexOf('-') + 1));
            
            // Compare numbers to find the largest
            return numA.CompareTo(numB);
        });
        
        // The last file in the sorted array is the largest one
        string largestFileName = Path.GetFileName(matchingFiles[matchingFiles.Length - 1]);
        Dts.Variables["User::MaxFileName"].Value = largestFileName;
        
        Dts.TaskResult = (int)DTSExecResult.Success;
    }
}

Next, set an expression on the @FullFilePath variable to combine the folder path and the largest filename:

1. In the Variables pane, right-click @FullFilePath -> Properties
2. Find the Expressions property, click the ellipsis (...)
3. In the Expression Builder, enter:

   @[User::FolderPath] + @[User::MaxFileName]
   

4. Click Evaluate Expression to verify it works, then click OK

Now link your Flat File Connection Manager to the dynamic @FullFilePath variable:

1. Right-click your Flat File Connection Manager -> Properties
2. Find the Expressions property, click the ellipsis (...)
3. Select ConnectionString from the property dropdown
4. In the Expression Builder, enter:

   @[User::FullFilePath]
   

5. Click OK to save the expression

Note: Make sure your Flat File Connection is configured correctly for your CSV format (delimiters, headers, data types) beforehand.

Add a Data Flow Task to your Control Flow, connect it to the Script Task (so it runs after the filename is captured), then configure the Data Flow:

1. Add a Flat File Source to the Data Flow, select your configured Flat File Connection Manager
2. Add an OLE DB Destination to the Data Flow, connect it to the Flat File Source
3. In the OLE DB Destination Editor:
   • Select your target SQL Server connection manager
   • For Data access mode, choose Table or view - fast load
   • In the Name of the table or view field, enter your global temp table name (e.g., ##ImportedCSVData)
   • Go to the Mappings tab to verify column mappings match your CSV and temp table structure
   • Click OK

• Global Temp Table Behavior: ##Temp tables are visible to all sessions and are automatically dropped when the last session using them closes. If you need to ensure the table is clean before import, you could add an Execute SQL Task (before the Data Flow) to run IF OBJECT_ID('tempdb..##ImportedCSVData') IS NOT NULL DROP TABLE ##ImportedCSVData—just make sure you have permissions to run that command.
• Error Handling: The Script Task includes error handling for missing files; you can extend this with event handlers if you need to log errors or take custom actions.
• Filename Variations: If your filename prefixes might change but the numeric suffix is always the key, the sorting logic in the Script Task is robust because it directly compares the numeric values (not just string sorting, which can fail for numbers with varying digit counts).

内容的提问来源于stack exchange，提问作者Mchief