none
SSIS - expression DATETIME

    Question

  • Hi,

    When I use this expression in BIDS (2005) it works fine: (DT_WSTR, 10) (DT_DBDATE) GETDATE()  + " " + (DT_WSTR, 8) (DT_DBTIME) GETDATE()
    But when I try it in BIDS (SQL 2008 R2; Visual Studio 2008 Version 9.0.30729.4462 QFE) - it throws this message:

    " A truncation occurred during evaluation of the expression"

    Please let me know regarding this - is this a bug ? .

    Thanks,
    NR.



    Tuesday, April 05, 2011 10:07 PM

Answers

  • I have tried this with separate fields and notice the problem is not with the DT_WSTR conversion but with the DT_DBTIME conversion.

    It tells me:

    The expression "(DT_DBTIME)[GETDATE]()" will always result in a truncation of data. The expression contains a static truncation (the truncation of a fixed value).

    However, by configuring the Error Output to Ignore Failure on Truncation of that particular column, I still end up with the time value there, which can then be converted to text as you desire.

    Tuesday, April 05, 2011 11:48 PM

All replies

  • What is the definition of the field it is going into? Is there any chance that could be too short to receive the formatted string?
    Tuesday, April 05, 2011 10:50 PM
  • I am trying to dynamically set the filename : filename_yyyymmddhhmmss.txt in the Flat File connection manager expression property:

    "\\\\networkpath\\fldr1\\ArchiveFiles\\filename"+replace((DT_WSTR, 10) (DT_DBDATE) GETDATE(),"-","")+replace((DT_WSTR, 8) (DT_DBTIME) GETDATE(),":","")  +".txt"

    and I keep getting the truncation message upon expression evaluation. If i remove the DT_DBTime syntax in the above line it works fine.

     

    I would like to get the time part working so that if I run the package again in the same day the archive file should not be overwritten as the time will vary.

     

    Tuesday, April 05, 2011 11:15 PM
  • I tried this : "\\\\networkpath\\fldr1\\ArchiveFiles\\filename"+(DT_WSTR,4)YEAR(GETDATE()) +  RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) +  RIGHT("0" + (DT_WSTR,2)DATEPART("hh", GETDATE()), 2) +  RIGHT("0" + (DT_WSTR,2)DATEPART("mi", GETDATE()), 2) + RIGHT("0" + (DT_WSTR,2)DATEPART("ss", GETDATE()), 2)+".txt"

    And this works fine.

    But still curious to know why the general dt_dbtime throws truncation error.

    Tuesday, April 05, 2011 11:23 PM
  • I have tried this with separate fields and notice the problem is not with the DT_WSTR conversion but with the DT_DBTIME conversion.

    It tells me:

    The expression "(DT_DBTIME)[GETDATE]()" will always result in a truncation of data. The expression contains a static truncation (the truncation of a fixed value).

    However, by configuring the Error Output to Ignore Failure on Truncation of that particular column, I still end up with the time value there, which can then be converted to text as you desire.

    Tuesday, April 05, 2011 11:48 PM
  • Thank you for the help and clarification.

    -Thanks

    NR

    Thursday, April 07, 2011 4:29 PM
  • than Q
    Jagalla
    Monday, August 01, 2011 3:29 PM