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.

Answer 1

Use parameters...

In OleDB source: http://beyondrelational.com/modules/2/blogs/101/posts/13429/ssis-pass-a-variable-to-a-ole-db-source-in-a-data-flow.aspx

In Execute SQL Task: http://technet.microsoft.com/en-us/library/ms140355.aspx

---

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

Answer 2

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

Answer 3

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.

Answer 4

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!!!

Answer 5

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.

Answer 6

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

Answer 7

many thanks ..