none
SSIS Today's date

    Question

  • I have an execute sql task and I have to store a global variable GivenDate = getdate().
    select @givendate = convert(varchar(10),getdate(),101)    I have selected the parameters and given @givendate  to SSIS variable Date
    I get the error "Executing the Query failed".

    How do I resolve this? Is there a better method to assign today's date to an SSIS variable?


    In the middle of difficulty lies opportunity.
    Wednesday, February 24, 2010 7:08 PM

Answers

  • Hi,

    Please follow these steps.

    1. Create a package scope variable "GivenDate", change its type to "string".

    2. From the toolbox, drag a Execute SQL Task into control flow panel.

    3. Double-click that task to edit it. In the general tab, use Single row as the result set, set the connection and type the SQL clause

    select convert(varchar(10),getdate(),101) into SQLStatement property.

    4. Click result set tab, add the following expression into it:

    0:User::GivenDate

    5. Run the package.




    Please feel free to let me know if I’ve misunderstood anything.
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Proposed as answer by Kunal Joshi Tuesday, March 02, 2010 5:26 AM
    • Marked as answer by Zongqing Li Wednesday, March 03, 2010 8:05 AM
    Tuesday, March 02, 2010 5:24 AM
  • Why are you doing that?  You don't need to use the Execute SQL Task to pass in a variable that contains a date just to get a variable out that contains a date.

    Just use the system variable.
    Todd McDermid's Blog
    Wednesday, February 24, 2010 9:21 PM

All replies

  • the GivenDate  variable is a package variable?
    if yes then you have to use

    select ? = convert(varchar(10),getdate(),101)

    and then map the variable with it..
    Let us TRY this |

    http://quest4gen.blogspot.com/
    Wednesday, February 24, 2010 7:16 PM
  • There are several system variables already defined that contain today's date (and time) - "System::StartTime" is one.
    Todd McDermid's Blog
    Wednesday, February 24, 2010 7:36 PM
  • Thanks.  I put this and in parameter mapping, User:Givendate  parameter name: 0 and parametersize:-1
    In Resultset, Resultname 0 and Variablename:GivenDate

    Still , I get the error.  Where do you think am wrong?
    In the middle of difficulty lies opportunity.
    Wednesday, February 24, 2010 8:11 PM
  • Why are you doing that?  You don't need to use the Execute SQL Task to pass in a variable that contains a date just to get a variable out that contains a date.

    Just use the system variable.
    Todd McDermid's Blog
    Wednesday, February 24, 2010 9:21 PM
  • Hi,

    Please follow these steps.

    1. Create a package scope variable "GivenDate", change its type to "string".

    2. From the toolbox, drag a Execute SQL Task into control flow panel.

    3. Double-click that task to edit it. In the general tab, use Single row as the result set, set the connection and type the SQL clause

    select convert(varchar(10),getdate(),101) into SQLStatement property.

    4. Click result set tab, add the following expression into it:

    0:User::GivenDate

    5. Run the package.




    Please feel free to let me know if I’ve misunderstood anything.
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Proposed as answer by Kunal Joshi Tuesday, March 02, 2010 5:26 AM
    • Marked as answer by Zongqing Li Wednesday, March 03, 2010 8:05 AM
    Tuesday, March 02, 2010 5:24 AM
  • Could you please explain what's the importance of "101" in the given statement below :

    convert(varchar(10),getdate(),101)


    And if i need the date to be 02032012 (as Todays date) what should be used instead 101?
    Friday, February 03, 2012 12:47 PM
  • 101 is the format of the date - check the table near the top of this page for what each format "code" looks like.  There doesn't appear to be a regional code for that format, so you'll have to build it yourself by converting calls to YEAR, MONTH, and DAY.
    Todd McDermid's Blog Talk to me now on
    • Proposed as answer by 2008 to 2012 Monday, September 24, 2012 12:43 PM
    Friday, February 03, 2012 7:51 PM
  • There is no need to use an Execute SQL task to this: just create the variable, set the property "EvaluateAsExpression" to true and enter the Expression Builder (the "..." button). In there put some lines to get the date and convert it as needed, and you can use this variable readily. Faster and cleaner.

    examples:

    (DT_STR, 4, 1252)  YEAR( GETDATE()  ) + "-" + RIGHT("0" + (DT_STR, 2, 1252)  MONTH( GETDATE()  ), 2) + "-"
    + RIGHT("0" + (DT_STR, 2, 1252)  DAY( GETDATE()  ), 2)
    you get => 2013-05-06

    or

    (DT_WSTR, 50) (DT_DBTIMESTAMP) getdate()  
    you get => 2013-05-06 16:18:36.441000000

    examples from:

    http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/ebc5427e-bb1d-4137-8062-dc6cfcfd9ddc/

    and

    http://consultingblogs.emc.com/jamiethomson/archive/2005/03/19/SSIS_3A00_-Evaluating-variables-as-expressions.aspx

    cya!

    ----

    thelonelydba.wordpress.com

    Monday, May 06, 2013 7:42 PM