SSIS中如何读取并将JSON的Balance值转换为货币类型？

I’ve run into this exact type conversion quirk with the KingswaySoft JSON Source component before—float-to-decimal/currency conversions can go haywire because of how the component handles floating-point precision and implicit scaling. Let’s break down the fix step by step:

Step 1: Force the Balance Field to String Type in JSON Source #

First, we need to bypass the component’s default float parsing entirely:

• Open the JSON Source editor and locate the Balance field in the data preview/columns list.
• Change its data type to string (this ensures the component reads the raw numeric value as text, avoiding any floating-point distortion).

Step 2: Convert the String to Decimal/Currency with a Derived Column #

Next, use a Derived Column transformation to safely convert the string to your desired type:

• Add a Derived Column component after the JSON Source.
• Create a new column (e.g., Balance_Decimal) with an expression tailored to your needs:
  • For decimal with 6 decimal places: (DT_DECIMAL,6)Balance
  • For currency (note: DT_CY uses 4 decimal places, so adjust if your values need more precision): (DT_CY)Balance
  • To handle nulls gracefully: ISNULL(Balance) ? NULL(DT_DECIMAL,6) : (DT_DECIMAL,6)Balance

Step 3: Troubleshoot with a Script Component (If Needed) #

If the Derived Column still throws errors or distorts values, use a Script Component (as a Transformation) for more control:

• Add the Script Component, select Balance as an input column.
• Add an output column (e.g., Balance_Clean) with data type decimal or currency.
• In the C# script, parse the string using invariant culture to avoid locale-specific decimal separator issues:

  public override void Input0_ProcessInputRow(Input0Buffer Row)
  {
      if (!Row.Balance_IsNull && !string.IsNullOrWhiteSpace(Row.Balance))
      {
          if (decimal.TryParse(Row.Balance, System.Globalization.NumberStyles.Any, System.Globalization.CultureInfo.InvariantCulture, out decimal result))
          {
              Row.BalanceClean = result;
          }
          else
          {
              // Handle invalid values as needed
              Row.BalanceClean_IsNull = true;
          }
      }
      else
      {
          Row.BalanceClean_IsNull = true;
      }
  }
  

Key Notes to Avoid Future Issues #

• Check JSON Format: Ensure your JSON file uses consistent numeric formatting (no mixed scientific notation and plain decimals).
• Precision Matching: Align your target data type’s decimal places with the values in your JSON (e.g., if values have 6 decimals, use DT_DECIMAL,6 instead of the default).
• Test with Single Rows: Validate conversions with individual problematic values first to confirm the fix works before scaling.

内容的提问来源于stack exchange，提问作者Толик Козедубов