none
Expression help in SSIS!!!

    Question

  • Hi,

    I have the below expression:

    ISNULL(@[User::PROCESS_DATE]) || TRIM (@[User::PROCESS_DATE]) == "" ? "1900-01-01" : (@[User::PROCESS_DATE]) == SUBSTRING(@[User::PROCESS_DATE],1,4) + "-" + SUBSTRING(@[User::PROCESS_DATE],5,2) + "-" + "1"

    i get the following error:

    The data types "DT_WSTR" and "DT_BOOL" are incompatible for the conditional operator

    The variable Process_date is char(6) and the destination column is datetime..

     

    Monday, July 18, 2011 4:04 PM

Answers

All replies

  • you have to add a cast to make it a datetime

    ISNULL(@[User::PROCESS_DATE]) || TRIM (@[User::PROCESS_DATE]) == "" ? (DT_DBTIMESTAMP)"1900-01-01" : (@[User::PROCESS_DATE]) == SUBSTRING(@[User::PROCESS_DATE],1,4) + "-" + SUBSTRING(@[User::PROCESS_DATE],5,2) + "-" + "1"

     

    I'm not getting the second part... in what format is your string variable with the date? Could you give an example? You have to cast that string as well...

     


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

     





    Monday, July 18, 2011 4:22 PM
  • The second argument to your conditional operator is:

    "1900-01-01"

    that is a DT_WSTR

     

    The third argument to your conditional operator is:

    (@[User::PROCESS_DATE]) == SUBSTRING(@[User::PROCESS_DATE],1,4) + "-" + SUBSTRING(@[User::PROCESS_DATE],5,2) + "-" + "1"

    that is a DT_BOOL.

     

    The second and third arguments to the conditional operator must have the same datatype. Yours, evidently, do not.

     

    hope that helps.

    @jamiet


    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Monday, July 18, 2011 4:23 PM
  • this is how i get the string 201105 just the month and the year,i need to put the first day of the month..
    Monday, July 18, 2011 5:20 PM
  • So,how shd i cast it from bool to date?

    Monday, July 18, 2011 5:21 PM
  • this is how i get the string 201105 just the month and the year,i need to put the first day of the month..


    Should be something like this:

    ISNULL(@[User::PROCESS_DATE]) || TRIM (@[User::PROCESS_DATE]) == "" ? (DT_DBTIMESTAMP)"1900-01-01" : (DT_DBTIMESTAMP)(Substring(@[User::PROCESS_DATE],1,4) + "-" + Substring(@[User::PROCESS_DATE],5,2) + "-1")

     

    If the string variable is empty or null you get a default date (in the format DT_DBTIMESTAMP) else you use the value if the string variable to create a date (which you also cast to a DT_DBTIMESTAMP)


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
    Monday, July 18, 2011 5:32 PM
  • SSIS JOOST,

     

          the above expression works fine when the value is 201105 but for null or blank values its throwing an error: for empty string


    ADDITIONAL INFORMATION:

    Error code 0x80020005 occurred attempting to convert from data type DT_WSTR to data type DT_DBTIMESTAMP.

    Casting expression "(SUBSTRING(@[User::PROCESS_DATE],1,4) + "-" + SUBSTRING(@[User::PROCESS_DATE],5,2) + "-1")" from data type "DT_WSTR" to data type "DT_DBTIMESTAMP" failed with error code 0xC00470C2.

     

    For NULL Values:


    ADDITIONAL INFORMATION:

    Error code 0x80020005 occurred attempting to convert from data type DT_WSTR to data type DT_DBTIMESTAMP.

    Casting expression "(SUBSTRING(@[User::PROCESS_DATE],1,4) + "-" + SUBSTRING(@[User::PROCESS_DATE],5,2) + "-1")" from data type "DT_WSTR" to data type "DT_DBTIMESTAMP" failed with error code 0xC00470C2.

     

    I used expression editor tool for evaluating this expression

    Monday, July 18, 2011 5:48 PM
  • Try small parts of the expression to see what they do. For example:

    ISNULL(@[User::PROCESS_DATE]) ? "default" : @[User::PROCESS_DATE]

    or

    TRIM(@[User::PROCESS_DATE]) == "" ? "default" : @[User::PROCESS_DATE]

    or

    ((TRIM(@[User::PROCESS_DATE]) == "" ) || (ISNULL(@[User::PROCESS_DATE]))) ? "default" : @[User::PROCESS_DATE]

    etc.

     

    Also see this handy tool:
    http://expressioneditor.codeplex.com/Wikipage?ProjectName=expressioneditor


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
    • Marked as answer by RAGS1109 Monday, July 18, 2011 8:19 PM
    Monday, July 18, 2011 6:03 PM
  • i finally,got this to work..

     

    ISNULL(@[User::PROCESS_DATE]) ? "1900-01-01" : TRIM (@[User::PROCESS_DATE]) == "" ? "1900-01-01": (Substring(@[User::PROCESS_DATE],1,4) + "-" + Substring(@[User::PROCESS_DATE],5,2) +  "-" + "01")

    i was using the same expression editor..

     

    one thing is weird tho..

     

    when i input NULL it returns me NULL--01 instead of 1900-01-01,it good for remaining conditions tho..

     

    Monday, July 18, 2011 6:26 PM
  • i finally,got this to work..

     

    ISNULL(@[User::PROCESS_DATE]) ? "1900-01-01" : TRIM (@[User::PROCESS_DATE]) == "" ? "1900-01-01": (Substring(@[User::PROCESS_DATE],1,4) + "-" + Substring(@[User::PROCESS_DATE],5,2) +  "-" + "01")

    i was using the same expression editor..

    If you want it to be in a datetime format you still have to add a CAST expression.

     

    one thing is weird tho..

     

    when i input NULL it returns me NULL--01 instead of 1900-01-01,it good for remaining conditions tho..

     


    Is it the text "NULL" or is it realy a null-value?
    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
    Monday, July 18, 2011 6:41 PM
  • it is text NULL i have given in the expression editor..
    Monday, July 18, 2011 6:56 PM
  • it is text NULL i have given in the expression editor..


    aha... then you don't have to use ISNULL(). You should check for the string "NULL"

     

    (UPPER(@[User::PROCESS_DATE]) == "NULL") || TRIM (@[User::PROCESS_DATE]) == "" ? (DT_DBTIMESTAMP)"1900-01-01" :(DT_DBTIMESTAMP)(Substring(@[User::PROCESS_DATE],1,4) + "-" + Substring(@[User::PROCESS_DATE],5,2) + "-1")


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
    Monday, July 18, 2011 7:03 PM
  • I have a same error and don't know the syntax to cast to compatible types.  Would appreciate help.

    @TargetExists is boolean & @DataDestinationTable is string.  I need to set the boolean variable to use in constraint for brancing between SQL Tasks

    "SELECT "+@[User::TargetExists] +" =  CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END
    FROM " +@[User::DataDestinationTable]    

    Friday, March 21, 2014 1:52 PM