October 20, 2024 at 7:02 pm
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
------------------------------
October 21, 2024 at 10:59 am
DT_DECIMAL does not have a precision. Use DT_NUMERIC in your formula instead.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply