SSIS中将FTP下载的.xlsx文件转换为.csv文件的技术求助

Hey there! I’ve dealt with this exact problem countless times—SSIS doesn’t have a built-in XLSX-to-CSV task, but there are three solid ways to get this done. Let’s walk through each one, starting with the Script Task fix since you already tried that (I bet the issue was missing references or cleanup code).

Method 1: Script Task (C# with EPPlus) #

EPPlus is a lightweight, open-source library for working with Excel files—no need to have Microsoft Office installed on your server, which is a huge plus. Here’s how to set it up:

1. Add the Script Task to your SSIS package, connect it right after your FTP Download task.
2. Edit the Script Task: Choose C# as the scripting language, then click Edit Script to open the code editor.
3. Add EPPlus Reference: Right-click the References folder in the Script Editor > Add Reference > Browse to where you’ve saved the EPPlus.dll (you can grab it from NuGet, then copy the DLL to a location accessible by your SSIS server).
4. Replace the default code with this:

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

namespace ST_XXXXXXXXX
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            // Pull paths from SSIS variables (set these up first!)
            string xlsxPath = Dts.Variables["User::XlsxFilePath"].Value.ToString();
            string csvPath = Path.ChangeExtension(xlsxPath, ".csv");

            try
            {
                using (ExcelPackage package = new ExcelPackage(new FileInfo(xlsxPath)))
                {
                    // Target the first worksheet—adjust index if you need a different sheet
                    ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
                    int rowCount = worksheet.Dimension.Rows;
                    int colCount = worksheet.Dimension.Columns;

                    using (StreamWriter sw = new StreamWriter(csvPath))
                    {
                        for (int row = 1; row <= rowCount; row++)
                        {
                            string[] rowValues = new string[colCount];
                            for (int col = 1; col <= colCount; col++)
                            {
                                // Replace commas with semicolons if your data has commas—tweak as needed
                                rowValues[col - 1] = worksheet.Cells[row, col].Text?.Replace(",", ";") ?? "";
                            }
                            sw.WriteLine(string.Join(",", rowValues));
                        }
                    }

                    Dts.TaskResult = (int)DTSExecResult.Success;
                }
            }
            catch (Exception ex)
            {
                Dts.Events.FireError(0, "XLSX to CSV Conversion", ex.Message, string.Empty, 0);
                Dts.TaskResult = (int)DTSExecResult.Failure;
            }
        }
    }
}

5. Set Up Variables: Create a User::XlsxFilePath variable that holds the full path to your downloaded XLSX file (your FTP task can populate this automatically, or you can set it manually).

Alternative for Office Interop: If you can’t use EPPlus, you can use Microsoft.Office.Interop.Excel instead (requires Office installed on the server). Just be sure to clean up COM objects to avoid memory leaks:

using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;

// Inside the Main method
Application excelApp = new Application();
Workbook workbook = excelApp.Workbooks.Open(xlsxPath);
Worksheet worksheet = workbook.Worksheets[0];

worksheet.SaveAs(csvPath, XlFileFormat.xlCSV);

// Critical cleanup steps
Marshal.ReleaseComObject(worksheet);
workbook.Close();
Marshal.ReleaseComObject(workbook);
excelApp.Quit();
Marshal.ReleaseComObject(excelApp);

Method 2: Execute Process Task with PowerShell #

If you prefer avoiding SSIS script code, use a PowerShell script for conversion—great for locked-down environments:

1. Create a PowerShell script (save as Convert-XlsxToCsv.ps1):

param(
    [string]$XlsxPath,
    [string]$CsvPath
)

# Install the ImportExcel module first with: Install-Module -Name ImportExcel
Import-Module ImportExcel

# Convert the first worksheet to CSV
Import-Excel -Path $XlsxPath | Export-Csv -Path $CsvPath -NoTypeInformation -UseCulture

2. Add an Execute Process Task to your package.
3. Configure the task:
   • Executable: powershell.exe
   • Arguments: -File "C:\Your\Script\Path\Convert-XlsxToCsv.ps1" -XlsxPath "$(User::XlsxFilePath)" -CsvPath "$(User::CsvFilePath)"
   • Ensure User::CsvFilePath points to your desired output location.

Method 3: Data Flow Task (No Code) #

This is the pure SSIS component approach—no scripting required:

1. Add a Data Flow Task after your FTP download task.
2. Inside the Data Flow:
   • Add an Excel Source and configure it to use your downloaded XLSX file (use a variable for the file path to handle dynamic names).
   • Add a Flat File Destination, create a new Flat File Connection Manager set to CSV format, then map all columns from the Excel Source to the destination.
3. Run the package: The Data Flow will read the XLSX and write directly to CSV.

Pro Tip: For dynamic file names, right-click your Excel Connection Manager > Properties > Expressions > set ConnectionString to your file path variable.

Any of these methods should work—my go-to is the Script Task with EPPlus because it’s lightweight and doesn’t depend on Office. If your previous Script Task failed, double-check that you added the correct references or that your file path variables were set up properly.

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