Hi Folks!!!!!!!, I have two column in a excel which of Boolean and Double Precision Float(DT_R8), not i have to check whether column has blank value in it, if it is i must change it to NULL and dump the same into the SQL Table. to acheive above goal, i have used below mention formula in derived column of ssis package.. TRIM(Column_Name)=="" ? NULL(DT_R8) : TRIM(Column_name) --- for double precision TRIM(Column_Name)=="" ? NULL(DT_BOOL) : TRIM(Column_name) --- for Boolean Both cases, throws error TRIM is not compatible with DT_R8 and Bool respectivley. Please suggest how to acheive above goal. Amit Please mark as answer if helpful http://fascinatingsql.wordpress.com/

Are you trying to TRIM a boolean or numeric field? It's a string function. You should add a CAST. PS change the thread type to question Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Hi SSISJoost, Thanks for your reply, but i found the solution before you suggest, and yes , it is same as you suggest. But i am not able to mark ypur post as answer as i didnt found any option of that. I used TRIM((DT_WSTR,255)(Column_Name))=="" ? NULL(DT_WSTR,255) : TRIM((DT_WSTR,255)(Column_Name)) --- for double precision and bool.Amit Please mark as answer if helpful http://fascinatingsql.wordpress.com/

Do you know what the TRIM function does? It removes leading and trailing whitespaces from strings. So of course it doesn't work on Boolean or Float values. You're essentially converting the boolean/float value to string - which will contain no whitespace - and then remove the non-existing whitespace. You're doing a zero operation. Furthermore, you cannot check if a boolean/float value is a blank value "", because that's only for strings. Make sure that Excel imports the values as the correct datatype. If a cell is empty, it will be imported as NULL. edit: you cannot assign posts as answered, because you made the thread type a discussion, instead of a question. MCTS, MCITP - Please mark posts as answered where appropriate.

But i am not able to mark ypur post as answer as i didnt found any option of that. It because of the thread type... you choose "General discussion", but it should be "Question". Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Do you know what the TRIM function does? It removes leading and trailing whitespaces from strings. So of course it doesn't work on Boolean or Float values. You're essentially converting the boolean/float value to string - which will contain no whitespace - and then remove the non-existing whitespace. You're doing a zero operation. Furthermore, you cannot check if a boolean/float value is a blank value "", because that's only for strings. Make sure that Excel imports the values as the correct datatype. If a cell is empty, it will be imported as NULL. edit: you cannot assign posts as answered, because you made the thread type a discussion, instead of a question. MCTS, MCITP - Please mark posts as answered where appropriate. Hi Amit, If your column is a boolean or some other non-string type, then it's better to use an ISNULL Expression ISNULL(Column_Name) ? **** : **** Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

hi SSISJOOST, ISNULL not working fine, as i have BLANK value not null value.Amit Please mark as answer if helpful http://fascinatingsql.wordpress.com/

hi SSISJOOST, ISNULL not working fine, as i have BLANK value not null value. How is that possible? A boolean has per definition only two values: true or false. In databases, this is extended with NULL. It's not possible for a boolean field to have a blank value, unless you unknowingly import those as strings instead of boolean values.MCTS, MCITP - Please mark posts as answered where appropriate.

Will this SSIS Expression help: Len(Column1)>0?NULL(DT_BOOL):Column1 Arthur My Blog

Will this SSIS Expression help: Len(Column1)>0?NULL(DT_BOOL):Column1 Since LEN is a string function, Column1 needs to be of a string datatype, which means the datatype of the first expression is DT_BOOL (you specified it with a cast), while the second one is of a string datatype. Since these are conflicting datatypes, this expression should result in an error. @Amit: what is the datatype of the columns in the dataflow? Right click on an arrow, choose data viewers and look in the metadata tab.MCTS, MCITP - Please mark posts as answered where appropriate.

Yes, that one, it will; we can tweak it, say with Len(Column_Name)>0?NULL(DT_WSTR,1):Column_Name, the important thing is to come up with the right remedy.Arthur My Blog