SSIS异常：仅错误输出设为重定向行时插入数据且字段异常

Let’s break down each problem and walk through actionable fixes—these are classic SSIS pitfalls, so you’re not alone!

Issue 1: SSIS only inserts data when error output is set to "Redirect Rows" #

This usually boils down to hidden errors in your data flow that halt the entire component when using default error handling. Here’s what to check:

• First, uncover the root error: Redirect the error output to a flat file or temporary SQL table (add a Flat File Destination/OLE DB Destination to the error path). Look at the ErrorCode and ErrorColumn values to pinpoint failures—common issues include data truncation (e.g., a source varchar(100) mapped to a target varchar(50)) or data type mismatches (e.g., numeric values stuffed into a string column).
• Check default error behavior: SSIS defaults to "Fail Component" mode—if even one row has an error, the whole component stops, and no data gets inserted (especially if your package uses transactions, which roll back all partial inserts). Switching to "Redirect Rows" siphons off bad rows, letting valid ones process and commit.
• Fix the underlying data issues: Once you know the error, clean up source data (trim long values, correct invalid types) or adjust component settings (increase target column length, use a Derived Column to cast values properly).
• Verify transaction settings: If your package’s TransactionOption is set to Required, any component failure triggers a full rollback. Try setting it to Supported temporarily to see if valid rows persist, then address error rows properly.

Issue 2: ODBC-to-SQL Server 2008 flow inserts only valid dates; other fields are spaces/NULL #

This is almost always a mapping, conversion, or ODBC driver problem. Let’s troubleshoot step by step:

• Validate end-to-end field mappings: Double-check every step from ODBC Source → Data Conversion → SQL Server Destination. Ensure each source field maps to the correct Data Conversion output, and those converted fields link to the corresponding target columns. It’s easy to leave fields unmapped, leading to NULLs or default spaces.
• Audit Data Conversion component settings: When converting to DT_STR, two settings are critical:
  • Code Page: Use the correct code page for your data (e.g., 1252 for ANSI, 65001 for UTF-8). Mismatched code pages can corrupt non-date fields.
  • Length: Set the length to match or exceed the source field’s length. For example, converting an int to DT_STR needs a length of at least 10; setting it to 1 will result in empty spaces.
• Preview source data: Right-click the ODBC Source and select "Preview"—if non-date fields are already empty here, the problem lies with your ODBC connection or source query. Verify your query selects all required fields, and the ODBC driver is configured to read the external database’s data types correctly.
• Bypass the conversion component (temporarily): If data types are compatible, map the ODBC Source directly to the SQL Server Destination. If this works, the issue is definitely in the Data Conversion component—go back and tweak its settings.
• Update your ODBC driver: Old or generic ODBC drivers struggle with certain data types. Use the official driver for your external database (e.g., PostgreSQL ODBC Driver, MySQL ODBC Connector) instead of a generic one, and ensure it’s compatible with SQL Server 2008.

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