PROD环境SSIS包执行无错但写入0行问题咨询

Alright, let's break down this puzzling issue—since your DEV and Prod environments were fully synced last week (data, SSIS packages, data sources) and the package runs without errors but leaves that third table empty, here are the most actionable areas to investigate step by step:

1. Confirm the Source Query Returns Data in Prod #

Even with synced packages, the underlying source data or query logic can drift in Prod. Do this first:

• Open the SSIS package, copy the exact query from the third table's OLEDB Source component.
• Run that query directly against the Prod source database using the same service account that executes the SSIS package (permissions can differ from your own account!).
• If the query returns 0 rows here, you've found your root cause. Check for:
  • Hardcoded filters (e.g., WHERE TransactionDate >= '2024-01-01' that's now out of range in Prod)
  • Environment-specific parameters (like a config table value that was updated in Prod post-sync)
  • Accidentally pointing to a deprecated/renamed source table or schema

2. Check for Silent Data Flow Failures #

SSIS can swallow errors if your data flow is configured to redirect rows instead of throwing exceptions. Look at the third table's data flow task:

• Error Output Configuration: Did you set the OLEDB Destination to redirect error rows to a flat file/table without monitoring it? Those rows might be getting dropped silently.
• Transformations Filtering Rows: Are there Derived Column, Lookup, or Conditional Split components before the destination that could be filtering all rows? For example, a Lookup set to "Redirect rows to no match output" that's sending every row to the error path.
• Triggers on the Target Table: Does the third table have an INSTEAD OF INSERT trigger that might be overriding the insert logic without throwing an error? Test inserting a single row manually into the Prod target table to verify it works as expected.

3. Validate Environment & Configuration Settings #

Synced packages don't guarantee synced configurations. Double-check these:

• SSIS Environment Variables: Ensure variables for the third table's source/destination connections are pointing to the correct Prod databases (not leftover DEV values).
• Package Configurations: If using XML/SQL Server configurations, confirm the Prod configuration wasn't modified after last week's sync. A connection string might have been accidentally updated to point to the wrong database/schema.
• Project-Level Connections: When deploying, connection managers can sometimes be overridden. Verify the project's connection strings in Prod match DEV exactly.

4. Look for Subtle Data Type or Constraint Issues #

Even synced schemas can have hidden mismatches that cause silent failures:

• Constraint Violations: Check the target table's unique/foreign key constraints. If source data has duplicates that violate a unique constraint, and the package is set to ignore these (unlikely, but possible if MaximumErrorCount is too high or constraint checking is disabled), rows might be dropped.
• Data Type Mappings: Review the data flow's column mappings. For example, a nvarchar(max) source column mapped to a nvarchar(255) destination with "Truncate strings" enabled could redirect rows to error paths if truncation occurs, without throwing a package-level error.

5. Dig Into Detailed Execution Logs #

Don't stop at the "success" status—get granular with SSIS logs:

• In SSMS, navigate to Integration Services Catalogs > Your Catalog > Projects > Your Project > Right-click Operations > View All Executions.
• Find the problematic run, then check the data flow statistics for the third table. It will show how many rows were read from the source, passed through transformations, and written to the destination.
  • If 0 rows are read: Source query/connection issue.
  • If rows are read but not written: Transformation or destination issue.
• Enable verbose logging temporarily for the package to capture step-by-step details of every component's behavior.

If none of these uncover the issue, try redeploying the DEV package directly to Prod to rule out sync gaps, or compare the XML code of the DEV and Prod packages to spot any hidden differences.

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