Put select statement in SSIS variable

Is it possible to add a variable in SSIS like

name of variable: myVar
Scope: Data Flow Task
Data Type: String
Value:SELECT hello FROM blah WHERE (azerty = @[User::pda]) AND (qwerty = @[User::phone])

@[User::pda] and @[User::phone] are also variables in SSIS just like the myVar I made

I know I'm doing something wrong with the data type because it's stores the whole select statement as a string

Help

Worf

[457 byte] By [Worf] at [2007-12-24]

«« Delete an element using Javascript?
»» beginer question -help

# 1

Worf wrote:

Is it possible to add a variable in SSIS like
name of variable: myVar
Scope: Data Flow Task
Data Type: String
Value:SELECT hello FROM blah WHERE (azerty = @[User::pda]) AND (qwerty = @[User::phone])
@[User::pda] and @[User::phone] are also variables in SSIS just like the myVar I made
I know I'm doing something wrong with the data type because it's stores the whole select statement as a string
Help
Worf

"SELECT hello FROM blah WHERE (azerty = " + @[User::pda] + ") AND (qwerty = " + @[User::phone] + ")"

This will work assuming the datatype of @[User::pda] & @[User::phone] is String. If not, you'll have to cast them.

-Jamie

JamieThomson at 2007-8-31 >

top of Msdn Tech,SQL Server,SQL Server Integration Services...

# 2

Looks exellent but I couldn"t get it to work.
I get an error that says the content of the variable is 544 characters long(yes I extended the select statement a bit and the select statement is actually 544 characters long).

But the result of the select statement is only a few charachters. SSIS is using the whole select statement but I need the result of that select statement, not the statement itself.

So then I tried the select statement in a OLE DB command task but it won"t exept the ssis variables (+ @[user::pda] +)

Any pointers?

Worf

Worf at 2007-8-31 >

# 3

Worf,

Are you using an expression in myvar variable to get the query? I guess you are trying to build the query dynamically; if so then few things:

OLE db command and OLE DB source only accepts SQL syntax; so +@[user::pda] + won’t work there. But they accept 'SQL Command from variable'

You should use a variable; like the one you are using myvar; then define an expression for that variable (when looking at the variable press F4 and you will see the properties, then specify 'true' for EvaluateAsExpression property and then click the expression property to get into the expression builder.

I hope this help

Rafael Salas

RafaelSalas at 2007-8-31 >

# 4