运行SSIS包时写入目标0行且缓冲区分配失败问题求助

Hey there, let’s work through this SSIS issue together—you’ve got all tasks showing success, but no rows are making it to your destination, plus those frustrating buffer allocation errors even after reducing buffer size. Let’s break down the possible causes and fixes:

• First, confirm if your source is actually returning data

  • Add a Row Count transformation right after your source component, link it to a package variable, and run the package. If the count is 0, your source isn’t pulling any rows at all—double-check your source query (maybe a too-strict WHERE clause?), connection string, or permissions on the source database. If the count is non-zero, the problem is happening between the source and destination.

• Hunt for silent row drops in transformations

  • Tasks showing "success" doesn’t mean rows aren’t getting dropped mid-flow. Enable Data Viewers between every component to trace where rows disappear. Common culprits:
    • A Conditional Split that filters out all rows (verify your condition logic)
    • Derived Columns causing errors where you’ve set error handling to "Ignore Failure" (rows get dropped without warning)
    • Lookup transformations set to drop non-matching rows (check the Lookup’s configuration if you expected those rows to pass through)

• Fix buffer allocation issues (it’s not just about size)

  • Reducing DefaultBufferSize might not be enough—try these adjustments:
    • Lower DefaultBufferMaxRows (found in the Data Flow Task properties) to limit how many rows are loaded into each buffer, which cuts down on memory usage.
    • Enable 64-bit runtime: Go to Project Properties > Debugging > set Run64BitRuntime to True (if you’re on a 64-bit machine, this gives SSIS access to more system memory).
    • Trim large data types early: If your flow uses TEXT/NTEXT or wide VARCHAR columns, cast them to smaller types or truncate unnecessary data before they hit the buffer—large columns bloat buffer size quickly.
    • Free up system memory: Close any other memory-heavy apps/services running on the server while executing the package—SSIS needs dedicated memory for buffer allocation.

• Double-check your destination component setup

  • Even if rows reach the destination, misconfiguration can lead to 0 rows written:
    • Verify the destination’s data access mode and write action: If you set it to "Truncate table" but no rows come in, you’ll end up with an empty table.
    • Confirm column mappings: Mismatched data types or missing mappings can cause rows to fail insertion silently—check the destination’s Error Output settings to see if rows are being redirected (instead of just dropped).
    • Ensure permissions: The account running the SSIS package needs INSERT (or UPDATE) permissions on the target table.

• Rule out transaction-related rollbacks

  • If your package uses transactions, a hidden failure could be rolling back all inserts:
    • Check if any component within the transaction scope is failing silently (even if the task shows success, a failed step might trigger a rollback).
    • Adjust the transaction isolation level—if it’s set too high, it might lock the destination table and prevent writes from completing.

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