Replacing Blanks values in Excel file while data conversion in SSIS

Problem:

Whenever yo have blank values in a column when you try to convert that using data conversion it will throw an error. How to resolver this

Solution

Step-1 -> Create a derived column Column

Breakdown of the Expression:

1. ISNULL([Latest Score]):

   • This part of the expression checks if the value in the Latest Score column is null.
   • ISNULL is a function that returns TRUE if the value is null and FALSE otherwise.

2. TRIM([Latest Score]) == "":

   • This part of the expression checks if the value in the Latest Score column is a blank string (i.e., an empty string).
   • TRIM is a function that removes leading and trailing spaces from the string.
   • The comparison == "" checks if the trimmed value is an empty string.

3. ||:

   • This is the logical OR operator.
   • The expression ISNULL([Latest Score]) || TRIM([Latest Score]) == "" evaluates to TRUE if either the value is null or it is a blank string.

4. ? 0 : (DT_NUMERIC, 10, 2) [Latest Score]:

   • This is a conditional (ternary) operator.
   • The syntax is condition ? value_if_true : value_if_false.
   • If the condition ISNULL([Latest Score]) || TRIM([Latest Score]) == "" is TRUE, the expression returns 0.
   • If the condition is FALSE, the expression converts the value in the Latest Score column to a numeric data type with a precision of 10 and a scale of 2.

Putting It All Together:

• Condition: ISNULL([Latest Score]) || TRIM([Latest Score]) == ""

  • Checks if the Latest Score is either null or a blank string.

• True Case: 0

  • If the condition is true (i.e., the value is null or blank), the expression returns 0.

• False Case: (DT_NUMERIC, 10, 2) [Latest Score]

  • If the condition is false (i.e., the value is neither null nor blank), the expression converts the value to a numeric data type with a precision of 10 and a scale of 2.

1. Handle Null and Blank Values:

   • Use a Derived Column transformation to handle null and blank values before the data conversion.
   • Replace null or blank values with default values that are compatible with the destination data type.

Example Steps:

1. Add Derived Column Transformation:

   • Drag and drop a Derived Column transformation from the SSIS Toolbox onto the Data Flow design surface.
   • Connect the output of the source component to the Derived Column transformation.

2. Configure Derived Column Transformation:

• Double-click on the Derived Column transformation to open its editor.
• Add a new derived column with an expression to handle null and blank values. For example:

  ISNULL([Latest Score]) || TRIM([Latest Score]) == "" ? 0 : (DT_NUMERIC, 10, 2) [Latest Score]
  

• This expression checks if Latest Score is null or blank and replaces it with 0. It also converts the value to a numeric data type with a precision of 10 and a scale of 2.

Example Configuration:

• Source Column: Latest Score (DT_WSTR)
• Derived Column: Latest Score_Derived (DT_NUMERIC, 10, 2) with expression:

  ISNULL([Latest Score]) || TRIM([Latest Score]) == "" ? 0 : (DT_NUMERIC, 10, 2) [Latest Score]
  

• Converted Column: Copy of Latest Score (DT_NUMERIC, 10, 2)

Additional Tips:

• Check Data Types:

  • Ensure that the data types of the source and destination columns are compatible.
  • Use the Data Viewer to inspect the data flow and verify that the transformations are working as expected.

• Handle Specific Data Types:

  • For numeric conversions, ensure that blank values are replaced with a default numeric value or null.
  • For date conversions, ensure that blank values are replaced with a default date or null.

• Check for Data Truncation:

  • Ensure that the destination column can accommodate the values from the source column without truncation.
  • For example, if the source column has a precision of 10 and a scale of 2, ensure that the destination column has at least the same precision and scale.