none
setting Experssion Query with Vairable

    Question

  • Hello Friends

    i'm using

    SELECT * FROM test.test WHERE NAME > cast(DATE(NOW()-INTERVAL 2 DAY) as datetime)

    This query at back-end and now in SSIS i have set below in parameter

    SELECT cast(DATE(NOW()-INTERVAL 2 DAY) as datetime) as synchDate

    i need this in my dataflow 

    SELECT * FROM test.test WHERE NAME >@synchDate

    now in Expression

    "SELECT * FROM test WHERE NAME= (DT_DATE) @[User::synchDate]"
    what should i do ?

    I'm using datetime variable.

    Kindly suggest me Expression Query i'm doing mistake in any Parentheses.

    and i do not know what to use

    (DT_DATE)

     (DT_STR, «length», «code_page»)

    Kindly help me.

    Saturday, January 05, 2013 9:54 AM

Answers

  • Create a variable test and set its evaluate expression property as true.where u will keep this expression

    (DT_STR, 4, 1252) DATEPART("yyyy", @[User::synchDate]) + "-" +
    (DT_STR, 2, 1252) DATEPART("mm", @[User::synchDate]) + "-" +
    (DT_STR, 2, 1252) DATEPART("dd", @[User::synchDate]) + " " +
    (DT_STR, 2, 1252) DATEPART("hh", @[User::synchDate]) + ":" +
    (DT_STR, 2, 1252) DATEPART("mi", @[User::synchDate]) + ":" +
    (DT_STR, 2, 1252) DATEPART("ss", @[User::synchDate]) + "." +
    (DT_STR, 3, 1252) DATEPART("ms", @[User::synchDate]) + "'"

    ps: the value that will come when u hit evalaute button will be default value which is there in [User::synchDate].

    now do what u were trying to do

    "SELECT * FROM test WHERE NAME= '"+ @[User::test]

    the other inverted codes at the beginning of the date will be coming from ur query "SELECT * FROM test WHERE NAME= '"

    so ur entire query will be something like this SELECT * FROM test WHERE NAME= '2013-1-7 1:53:16.0' when u press evaluate button

    • Marked as answer by PrajapatiNeha Monday, January 07, 2013 8:41 AM
    Monday, January 07, 2013 7:59 AM

All replies

  • Saturday, January 05, 2013 11:09 AM
  • I think you are using this in OLE DB Source

    Use : Select * from test where Name = Convert(date,?)

    Then in parameter mapping use variable @[user::synchDate].

    Please mark it as helpful if it helps.

    Thanks

    Sumit

    Sunday, January 06, 2013 5:44 AM
  • thanks for the reply

    No , i'm using I'm using MYSQL as an source and SqlServer as an destination .

    I resolved my issue by using below Query in expression.

    "SELECT * FROM test WHERE NAME=
     '" +
    (DT_STR, 4, 1252) DATEPART("yyyy", @[User::synchDate]) + "-" +
    (DT_STR, 2, 1252) DATEPART("mm", @[User::synchDate]) + "-" +
    (DT_STR, 2, 1252) DATEPART("dd", @[User::synchDate]) + " " +
    (DT_STR, 2, 1252) DATEPART("hh", @[User::synchDate]) + ":" +
    (DT_STR, 2, 1252) DATEPART("mi", @[User::synchDate]) + ":" +
    (DT_STR, 2, 1252) DATEPART("ss", @[User::synchDate]) + "." +
    (DT_STR, 3, 1252) DATEPART("ms", @[User::synchDate]) + "'"

    but Actually this is two long two write in every task where i need variable Value.

    so i'm using Alternative for this ..

    for OLEDB i simply use  ? for variable value.

     So Please suggest with some simple sign etc.

    Monday, January 07, 2013 5:32 AM
  • but Actually this is two long two write in every task where i need variable Value.



    if writing all this things is a pain for u.y cant u create one more variable which is having evaluate a expression property as TRue and use this (DT_STR, 4, 1252) DATEPART("yyyy", @[User::synchDate]) + "-" +
    (DT_STR, 2, 1252) DATEPART("mm", @[User::synchDate]) + "-" +
    (DT_STR, 2, 1252) DATEPART("dd", @[User::synchDate]) + " " +
    (DT_STR, 2, 1252) DATEPART("hh", @[User::synchDate]) + ":" +
    (DT_STR, 2, 1252) DATEPART("mi", @[User::synchDate]) + ":" +
    (DT_STR, 2, 1252) DATEPART("ss", @[User::synchDate]) + "." +
    (DT_STR, 3, 1252) DATEPART("ms", @[User::synchDate]) + "'"

    in a single variable than writing all the expression always!!!

    Monday, January 07, 2013 6:19 AM
  • Many thanks for the reply

    i think your suggestion can help me .

    but sorry i could not understand the exactly .

    can you help explain this with an example or share any links ?

    what i do i create one varible as an date time (or any other) and in properties tab evaluate a expression property as TRue than in expression i write

    (DT_STR, 4, 1252) DATEPART("yyyy", @[User::synchDate]) + "-" +
    (DT_STR, 2, 1252) DATEPART("mm", @[User::synchDate]) + "-" +
    (DT_STR, 2, 1252) DATEPART("dd", @[User::synchDate]) + " " +
    (DT_STR, 2, 1252) DATEPART("hh", @[User::synchDate]) + ":" +
    (DT_STR, 2, 1252) DATEPART("mi", @[User::synchDate]) + ":" +
    (DT_STR, 2, 1252) DATEPART("ss", @[User::synchDate]) + "." +
    (DT_STR, 3, 1252) DATEPART("ms", @[User::synchDate]) + "'"

    by clicking on evaluation button i got

    1900-1-1 0:0:0.0'

    but  i need one inverted comma before value as well.

    Monday, January 07, 2013 6:42 AM
  • Create a variable test and set its evaluate expression property as true.where u will keep this expression

    (DT_STR, 4, 1252) DATEPART("yyyy", @[User::synchDate]) + "-" +
    (DT_STR, 2, 1252) DATEPART("mm", @[User::synchDate]) + "-" +
    (DT_STR, 2, 1252) DATEPART("dd", @[User::synchDate]) + " " +
    (DT_STR, 2, 1252) DATEPART("hh", @[User::synchDate]) + ":" +
    (DT_STR, 2, 1252) DATEPART("mi", @[User::synchDate]) + ":" +
    (DT_STR, 2, 1252) DATEPART("ss", @[User::synchDate]) + "." +
    (DT_STR, 3, 1252) DATEPART("ms", @[User::synchDate]) + "'"

    ps: the value that will come when u hit evalaute button will be default value which is there in [User::synchDate].

    now do what u were trying to do

    "SELECT * FROM test WHERE NAME= '"+ @[User::test]

    the other inverted codes at the beginning of the date will be coming from ur query "SELECT * FROM test WHERE NAME= '"

    so ur entire query will be something like this SELECT * FROM test WHERE NAME= '2013-1-7 1:53:16.0' when u press evaluate button

    • Marked as answer by PrajapatiNeha Monday, January 07, 2013 8:41 AM
    Monday, January 07, 2013 7:59 AM
  • many thanks ..
    Monday, January 07, 2013 8:41 AM