SQL Server 2008运行SSIS包报错，请求错误日志排查建议

Hey there, let’s work through why your SSIS package is failing when processing that 8000+ row Excel file on your 64-bit SQL Server 2008 setup. I’ve seen this scenario a lot, so here are the most common culprits and step-by-step fixes to troubleshoot:

1. Fix 64-bit vs 32-bit Driver Compatibility #

SQL Server 2008’s 64-bit runtime doesn’t include native 64-bit Excel drivers by default. If your package uses the Excel Connection Manager, it’s probably trying to access a 32-bit driver that’s unavailable in 64-bit mode—this is one of the most frequent issues here.

• If running via SQL Server Agent: Go to your job step properties → Execution Options → check the box for "Use 32-bit runtime".
• If running from SSDT/BIDS: Open your project properties → Debugging → set Run64BitRuntime to False.

2. Resolve Excel Data Format/Corruption Issues #

Even 8000 rows can expose hidden formatting problems that SSIS (being strict about data consistency) will choke on. Common issues include mixed data types in a single column, merged cells, or broken records.

• Quick checks:
  • Open the Excel file manually and verify all columns have consistent data types (no random text in a numeric column, for example).
  • Add a Data Viewer between your Excel Source and the next component to pinpoint exactly which row is causing the failure.
  • Use the Error Output on the Excel Source to redirect bad rows to a flat file or table instead of crashing the whole package. Configure this by right-clicking the Excel Source → Edit → Error Output.

3. Address Memory/Resource Constraints #

While 8000 rows isn’t massive, complex transformations or competing system processes can hit memory limits.

• Try these fixes:
  • Temporarily disable non-critical transformations to isolate if one is draining resources.
  • Check the Windows Event Viewer (System and Application logs) for memory-related errors around the time the package failed.
  • If all else fails, split the Excel file into smaller chunks (though this shouldn’t be necessary for 8k rows, it can help narrow down issues).

4. Verify Your Excel Connection String #

Incorrect connection settings often cause failures, especially when mixing .xls and .xlsx files.

• Double-check the provider and properties:
  • For .xls files: Use provider Microsoft.Jet.OLEDB.4.0 with Extended Properties="Excel 8.0;HDR=YES;"
  • For .xlsx files: Use provider Microsoft.ACE.OLEDB.12.0 with Extended Properties="Excel 12.0 Xml;HDR=YES;"
  • Ensure the file path is correct (avoid network paths unless the SQL Server service account has full access to the shared folder).

5. Confirm Service Account Permissions #

The account running your SSIS package (your user account in BIDS, or the SQL Server Agent service account) might lack permissions to read the Excel file or write to the destination.

• Checklist:
  • Does the account have read permissions on the folder containing the Excel file?
  • If writing to a SQL Server table, does the account have INSERT/UPDATE permissions on that table?

6. Dig Into the Exact Error Log Details #

You mentioned an error log—don’t skip this! Specific error codes and messages will point you straight to the issue. For example:

• Error code 0xC0202009 usually means a connection problem.
• Error code 0xC020902A indicates a data type mismatch between Excel and your destination.

Extract the full error message and cross-reference it with SQL Server documentation—it’ll often give you a direct fix.

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