June 21, 2011 at 11:32 am
I have a CSV file where some of the date fields is an empty string. I need to transform this into a NULL and found this code
TRIM( [ColumnName] ) == “” ? (DT_STR, 8, 1252)NULL(DT_STR, [length], 1252) : [ColumnName]
I replaced "ColumnName" with my column "Position Entry Date" and the length to 50 as that is the length of the source. So now I have this
TRIM( [Position Entry Date] ) == “” ? (DT_STR, 8, 1252)NULL(DT_STR, 50, 1252) : [Position Entry Date]
When I click OK in Derived Column Transformation Editor, I get this error message.....
TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [Derived Column [1146]]: Attempt to parse the expression "TRIM( [Position Entry Date] ) == “” ? (DT_STR, 8, 1252)NULL(DT_STR, 50, 1252) : [Position Entry Date]" failed. The token " " at line number "1", character number "36" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.
Error at Data Flow Task [Derived Column [1146]]: Cannot parse the expression "TRIM( [Position Entry Date] ) == “” ? (DT_STR, 8, 1252)NULL(DT_STR, 50, 1252) : [Position Entry Date]". The expression was not valid, or there is an out-of-memory error.
Error at Data Flow Task [Derived Column [1146]]: The expression "TRIM( [Position Entry Date] ) == “” ? (DT_STR, 8, 1252)NULL(DT_STR, 50, 1252) : [Position Entry Date]" on "input column "Position Entry Date" (2808)" is not valid.
Error at Data Flow Task [Derived Column [1146]]: Failed to set property "Expression" on "input column "Position Entry Date" (2808)".
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
June 21, 2011 at 2:07 pm
Your casting syntax is wrong. Also, I use unicode strings when Im dealing with nulls, and then cast it back to non-unicode if I have to afterwards try:
(DT_STR,8,1252)(TRIM([Position Entry Date]) == ""?NULL(DT_WSTR, 50):(DT_WSTR,50)[Position Entry Date])
June 21, 2011 at 2:09 pm
Ha, forgot to mark it as code:
(DT_STR,8,1252)(TRIM([Position Entry Date]) == "" ? NULL(DT_WSTR, 50) : (DT_WSTR,50)[Position Entry Date])
June 22, 2011 at 8:13 am
Thanks Mark that worked. I am an SSIS newbie so I have do not understand this logic (I dont understand why it doesnt use VB expressions or SQL functions). Can you walk me through it?
June 22, 2011 at 10:26 am
The bad syntax in your expression looks to be a misplaced :
In an SSIS expression If/Then Else statement, the Then clause follows the ? and the Else clause follows the :
[varA] == [varB] ? [varResult] = "Equal" : [varResult] = "Not equal"
Variable names can be enclosed in square brackets []
To cast a value to a data type, you put the data type name in parens () before the variable
HTH,
Rob
June 22, 2011 at 12:39 pm
rgtft (6/22/2011)
The bad syntax in your expression looks to be a misplaced :In an SSIS expression If/Then Else statement, the Then clause follows the ? and the Else clause follows the :
[varA] == [varB] ? [varResult] = "Equal" : [varResult] = "Not equal"
Variable names can be enclosed in square brackets []
To cast a value to a data type, you put the data type name in parens () before the variable
HTH,
Rob
Great that makes sense except for one part:
NULL(DT_WSTR, 50)
Why is there a cast AFTER the NULL?
June 22, 2011 at 1:03 pm
It's a function that takes a parameter as the type...don't ask me why, I have no idea.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply