Teradata转SQL Server时Unicode列长度冲突问题求助

Let's break down the core issue first: The Attunity Teradata Connector treats the source Description column (40-byte non-Unicode) as a single-byte string, while your target SQL Server column is a 20-character Unicode type (which uses 2 bytes per character, equivalent to 40 bytes total). The error message mentioning "Target column's data length is (60)" suggests there might be a mismatch between your ETL package's target metadata and the actual SQL Server table definition—let's fix that first, then address the character encoding properly.

Step 1: Align ETL Target Metadata with SQL Server Table #

• Open your SSIS package, go to the ADO.NET Destination component, and launch its Advanced Editor.
• Navigate to the Input and Output Properties tab, locate the Description column under the target output.
• Set its data type to DT_WSTR with length 20 (matching your actual SQL Server table's Unicode column length). Save this change—this resolves the "conflicting data length" mismatch triggering the initial error.

Step 2: Properly Convert Teradata Non-Unicode to Unicode (Preserve Special Characters) #

Your earlier Translate approach broke special characters because it converted Unicode to Latin, which doesn't support Cyrillic (Russian) characters. Instead, convert the source column to Unicode directly in the Teradata source query to ensure the connector reads it as a Unicode string:

SELECT 
    CAST(Description AS VARCHAR(40) CHARACTER SET UNICODE) AS Description,
    -- Add your other columns here
FROM bd

This tells Teradata to output the column as Unicode, so the Attunity connector will recognize it as DT_WSTR(40) (40 bytes = 20 Unicode characters), which maps perfectly to your target's 20-character Unicode column.

Step 3: Fix Attunity Connector Type Mapping (If Needed) #

If the above query doesn't automatically set the source column to Unicode, adjust the connector's advanced settings correctly:

1. Open the MS Connector for Teradata source's Advanced Editor.
2. Go to Input and Output Properties > TD Source Output > External Columns > Find Description.
3. Set its data type to Unicode String [DT_WSTR] with length 40.
4. Repeat the same for the corresponding Output Column under the same output node.
5. Save the package and re-run—this ensures the connector passes the data as Unicode from source to target without length conflicts.

Why Your Previous Attempts Failed #

• Translate(Description using unicode_to_latin with error): This converts Unicode characters to Latin-1, which can't represent Cyrillic, hence the garbled text.
• Forcing (DT_WSTR,40) in the expression: This only casts the column name (not the actual data) to a string, which doesn't affect the data's encoding or length.
• Changing connector properties without adjusting the source query: The Attunity connector might override manual type changes if the source query returns a non-Unicode column, so converting in the query is more reliable.

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