部署后SSIS无法读取UNC路径下的Excel文件求助

Let’s work through this—since your package runs fine in SQL Data Tools but breaks after deployment, the issue is almost certainly tied to differences between your local debug environment and how the server executes the package. Here are the most targeted fixes to try:

1. Double-check file permissions (beyond basic access) #

Even if your SQL service account can browse the UNC folder, the Jet Database Engine needs more than just read access:

• Ensure the account has read AND write permissions on both the Excel file and its parent folder. Jet creates a temporary lock file (like ~$yourfile.xls) in the same directory when opening the Excel file—without write access, this will trigger that 0x80004005 error.
• Verify no other process is locking the Excel file: Use Process Explorer on the server to check if Excel, another SSIS package, or a backup tool has exclusive access to the file.

2. Validate 32-bit runtime configuration (server-side) #

You mentioned setting 32-bit runtime in SDT and job configs, but let’s confirm it’s fully applied on the server:

• For SQL Server Agent Jobs:
  • Go to the job step’s configuration, under the "Execution options" tab, double-check that "Use 32-bit runtime" is checked.
  • Also, make sure the job uses a SQL Server Agent Proxy configured with the same account you used in SDT. The default agent account often has different permissions than your debug account.
• For SSIS Catalog (SSISDB) Deployments:
  • When executing the package via SSMS, go to the "Advanced" tab in the execution wizard and explicitly check "Use 32-bit runtime". Debug settings from SDT don’t carry over to SSISDB execution automatically.

3. Verify 32-bit Jet Driver installation & configuration #

Excel 97-2003 relies on the 32-bit Microsoft Jet 4.0 OLE DB Driver. On a 64-bit server:

• Install the 32-bit Jet driver if it’s missing (it comes with older Office versions, or you can get it via the Microsoft Access Database Engine redistributable).
• Open the 32-bit ODBC Administrator (C:\Windows\SysWOW64\odbcad32.exe) and check for a valid "Microsoft Excel Driver (*.xls)" entry. Test a connection here to rule out driver-level issues.

4. Ensure UNC path access is direct (no mapped drives) #

Service accounts don’t recognize mapped network drives—always use the full UNC path (e.g., \\fileserver\share\data\old_excel.xls) in your SSIS connection manager.

• To confirm the service account can access the path: Log into the server using the SQL service account, open File Explorer, navigate directly to the UNC path, and try opening the Excel file manually. This will catch any hidden permission prompts or file locks.

5. Check server-side event logs for extra context #

If the above steps don’t fix things, dig into the Windows Event Viewer on the server:

• Look for errors under Windows Logs > Application from sources like SQLISPackage100 or MSSQLSERVER. These logs often have more detailed messages that can pinpoint the exact permission or driver issue.

Let me know if any of these steps resolve your problem, or if you need help narrowing down a specific error detail!

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