Converting Non Numeric to Null

• yrstruly

  Hall of Fame

  Points: 3477

  More actions

  October 20, 2024 at 7:00 pm

  #4470911

  I have a numeric column called "Value" in a flat file, which contains both numeric data and non-numeric values, such as "X". I'm using the Derived Column Task in SSIS to convert the non-numeric "X" values to NULL. However, I am encountering an error during this process. Could you please assist me in resolving this issue?

  [Value] == "X" || [Value] == "-" || LEN(TRIM([Value])) == 0 ? NULL(DT_DECIMAL, 18, 2) : (DT_DECIMAL, 18, 2) REPLACE([Value], ",", ".")
  
  
  TITLE: Microsoft Visual Studio
  ------------------------------
  
  Error at Data Flow Task [Derived Column [2]]: Attempt to parse the expression "[Value] == "X" || [Value] == "-" || LEN(TRIM([Value])) == 0 ? NULL(DT_DECIMAL, 18, 2) : (DT_DECIMAL, 18, 2) REPLACE([Value], ",", ".")" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.
  
  Error at Data Flow Task [Derived Column [2]]: Cannot parse the expression "[Value] == "X" || [Value] == "-" || LEN(TRIM([Value])) == 0 ? NULL(DT_DECIMAL, 18, 2) : (DT_DECIMAL, 18, 2) REPLACE([Value], ",", ".")". The expression was not valid, or there is an out-of-memory error.
  
  Error at Data Flow Task [Derived Column [2]]: The expression "[Value] == "X" || [Value] == "-" || LEN(TRIM([Value])) == 0 ? NULL(DT_DECIMAL, 18, 2) : (DT_DECIMAL, 18, 2) REPLACE([Value], ",", ".")" on "Derived Column.Inputs[Derived Column Input].Columns[Value]" is not valid.
  
  Error at Data Flow Task [Derived Column [2]]: Failed to set property "Expression" on "Derived Column.Inputs[Derived Column Input].Columns[Value]".
  
  
  
  ------------------------------
  ADDITIONAL INFORMATION:
  
  Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)
  
  ------------------------------
  BUTTONS:
  
  OK
  ------------------------------
  

   

  Attachments:

  You must be logged in to view attached files.

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  October 21, 2024 at 7:10 pm

  #4471314

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.