如何在SSIS 2019数据流中直接将Microfocus COBOL COMP字段转换为整数（含正负值）

Absolutely! You can replicate that T-SQL logic directly in an SSIS 2019 Data Flow. Here are two straightforward approaches to handle both positive and negative Microfocus COBOL COMP field conversions:

This is the simplest method if you prefer avoiding custom code. You can build an expression that mirrors your T-SQL logic to handle both value types in one step.

Assuming your raw binary COMP field is named RawCompData (adjust to match your actual column name):

(RawCompData < 0x8000000000 ? 
    (DT_I8)RawCompData : 
    (DT_I8)RawCompData - (DT_I8)0xFFFFFFFFFF - 1
)

Expression Breakdown: #

• The condition RawCompData < 0x8000000000 checks for positive values (since the highest bit in a 5-byte signed binary acts as the sign bit; values below this threshold are positive).
• For positive values: We directly cast the binary data to DT_I8 (BigInt), just like your CONVERT(bigint, compField, 1) in T-SQL.
• For negative values: We apply the exact calculation from your T-SQL logic—subtract the maximum 5-byte unsigned value (0xFFFFFFFFFF) and subtract 1 to get the correct negative integer.

If you need more flexibility (like handling variable-length COMP fields or adding validation), a Script Component is a solid option. Here's how to implement it:

1. Add a Script Component to your Data Flow, set it as a Transformation.
2. In the Script Editor, add your raw binary column (RawCompData) as an input column.
3. Create a new output column (e.g., ConvertedCompValue) with data type DT_I8.
4. Replace the Input0_ProcessInputRow method with this code:

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    if (!Row.RawCompData_IsNull)
    {
        byte[] compBytes = Row.RawCompData;
        // Reverse bytes if needed: COBOL uses big-endian, SSIS reads as little-endian by default
        Array.Reverse(compBytes);
        
        ulong unsignedValue = BitConverter.ToUInt64(compBytes, 0);
        
        // Check if the sign bit (highest bit of 5-byte value) is set for negatives
        if ((unsignedValue & 0x8000000000) != 0)
        {
            Row.ConvertedCompValue = (long)(unsignedValue - 0xFFFFFFFFFF - 1);
        }
        else
        {
            Row.ConvertedCompValue = (long)unsignedValue;
        }
    }
    else
    {
        Row.ConvertedCompValue_IsNull = true;
    }
}

Critical Note: #

COBOL COMP fields are usually stored in big-endian format, but SSIS reads binary data as little-endian by default. The Array.Reverse(compBytes) line adjusts for this—test with your sample values first to confirm if you need this step (your T-SQL example uses 0x0000002B17 which is big-endian for 11031; if your SSIS source already reads bytes correctly, you might skip reversing).

• Test with your sample values to confirm:
  • 0x0000002B17 should return 11031
  • 0xFFFFFFD4E9 should return -11031
• Ensure your raw binary column in SSIS uses the DT_BYTES type with length 5 (matching your 5-byte COMP fields).

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