none
SSIS GETDATE() expression returning null or blank value

    Question

  • Using SQL2008 SP3, I have an SSIS package that uses an SQL Task. Within it, I have the following expresion to get the current date and time and insert it in a table:

    SUBSTRING((DT_WSTR, 50) GETDATE(), 1,  FINDSTRING((DT_WSTR, 50) GETDATE(), ".",1) -1)

    it works great 99.9% of the time, yet once in a while, I get the following error message when evaluationg this expression:
    The length -1 is not valid for function "SUBSTRING". The length parameter cannot be negative. Change the length parameter to zero or a positive value.

    So, my guess is that on that run the GETDATE() function returned null or blank or something that was not able to be used by the SUBSTRING function.

    Any ideas what could be wrong? Is it just a hick up in the system?

    Thanks so much for your feeback.


    Paulino

    Wednesday, June 26, 2013 6:30 PM

Answers

All replies

  • My only guess is when your server is under stress the portion after the . (microseconds) of the time returned by GETDATE() e.g. 2013-06-27 11:27:13.313000000 is not there and therefore the expression fails.


    Arthur My Blog

    Thursday, June 27, 2013 3:29 PM
    Moderator
  • The above experession evaluates to

    2013-06-27 17:01:20

    Do you intend to see this format of date?

    In order to get this format, use exec sql task and use this expreession in tha task

    select CONVERT(varchar,GETDATE(),120)

    save this result set into a variable, that can be later used in insert statemnt to dest table.


    Thanks, hsbal

    Thursday, June 27, 2013 9:06 PM
  • Thanks Arthur, your guess is my guess now. I appreciate it.

    Paulino

    Monday, July 01, 2013 1:45 PM
  • Yes Harry, that was my intent.

    Thanks for the suggestion.

    As a solution I am using a vb script to get the date and assign to a variable and then use it later.

    Thanks!


    Paulino

    Monday, July 01, 2013 1:47 PM