I have a similar problem. I am bulk loading data in from a pipe delimited flat file. I have a column that's supposed to be numeric (precision 18, scale 0) but there's several question marks in the data. Ex: 1025.12 15879.19 54.17 ? 889.78 485.26 I'm trying to replace the "?" with a 0 but can't make sense of the error output from SSIS: Here's my expression: [Column 55] == "?" ? REPLACE([Column 55], "?", 0) How can I write this expression so the data looks like: 1025.12 15879.19 54.17 0 889.78 485.26 TITLE: Microsoft Visual Studio ------------------------------ Error at NEW_POSDATA_ARCHIVE [Derived Column [1185]]: Attempt to parse the expression "[Column 55] == "?" ? REPLACE([Column 55], "?", 0)" 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 NEW_POSDATA_ARCHIVE [Derived Column [1185]]: Cannot parse the expression "[Column 55] == "?" ? REPLACE([Column 55], "?", 0)". The expression was not valid, or there is an out-of-memory error. Error at NEW_POSDATA_ARCHIVE [Derived Column [1185]]: The expression "[Column 55] == "?" ? REPLACE([Column 55], "?", 0)" on "input column "Column 55" (2025)" is not valid. Error at NEW_POSDATA_ARCHIVE [Derived Column [1185]]: Failed to set property "Expression" on "input column "Column 55" (2025)". ------------------------------ ADDITIONAL INFORMATION: Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap) ------------------------------ BUTTONS: OK ------------------------------

I've split this off from the thread you posted to (REPLACE in Derived Column transform causing repeat data in rows that should be blank), because it's very different. You're attempting to use a Conditional expression - "if this is true, do this, otherwise, do that"... but you haven't specified a "that". Change your expression to [Column 55] == "?" ? REPLACE([Column 55], "?", 0) : [Column55] You also likely have a problem (whether SSIS is reporting it or not) with data types. In the above expression, you're assuming that Column55 is a string type column (since you're comparing it to a question mark), yet you're changing that to a zero integer, not to a string of zeroes. In this case, it shouldn't matter, SSIS will convert that zero to a string with a zero in it. I just wanted you to be aware of the implicit conversion. Talk to me now on

I think a simpler approach could be: [Column 55] == "?" ? "0" : [Column55] As the condition you are checking is not something of a wild card. I also assume that the output of the column will be string as ? cannot be numeric.My Blog | Ask Me

I think a simpler approach could be: [Column 55] == "?" ? "0" : [Column55] As the condition you are checking is not something of a wild card. I also assume that the output of the column will be string as ? cannot be numeric.My Blog | Ask Me