Using Execute SQL Task Result Set to populate variable's EXPRESSION, not VALUE

hi there,

i'm trying dynamically create select statements in package. i'm using expressions in way you'd expect i.e. expression on vaiable set this:

"select synchdel,synchrec,pcode,pavailable,pprice,pdcode,pocode "+@[template::dbname]+".property pcode=''fak080032''"

which means value on variable evaluates as:

select synchdel,synchrec,pcode,pavailable,pprice,pdcode,pocode abnrps.property pcode=''fak080032''


or alternatively, want run

"select synchdel,synchrec,pcode,pavailable,pprice,pdcode,pocode "+@[template::dbname]+".property synchrec >  date_add(now(),interval -1 hour)"

which evalutes as

select synchdel,synchrec,pcode,pavailable,pprice,pdcode,pocode abnrps.property synchrec >  2010-01-19 12:05


point being, want able choose query run during package execution


now want store expressions in in database table, , read them package @ run time. idea doing run simple execute sql task returns expression string in resultset, , stores in vaiable. later on in package, use variable datasource in dataflow task (i know cannot dynamically configure number of columns etc - changes between expressions)

however, i'm having problem doing this, appears resultset of execute sql taks populates value of variable. need to populate expression on variable. so, though, variable set evaluateasexpression = true, getting value set, not expression, , therefore can't evaluate properly.

i can kinda prove this, becuase if store expression in [ssis configurations], , bring in through configuration, gives me option configure \package.variables[template::selectquery2].properties[expression], works great. (configuring won't work proper solution though, becuase need more flexibility decide select need during package execution). thinking execute sql task uses resultset populate \package.variables[template::selectquery2].properties[value], doesn't work.

so, a few questions: i'm describing above make sense, or missing somethign obvious? other ways have dynamically set expression property of variable? ways guys doing sort of thing?

this not possible if set value of expression variable , expect expression evaluated not possible.
what can instead split ur query 3 parts:

"select synchdel,synchrec,pcode,pavailable,pprice,pdcode,pocode "
@[template::dbname]
".property pcode=''fak080032''"

now save 1st & last in separate column.
and build ur query as:
@[user::qry1] + @[template::dbname] + @[user::qry2]

---

hope helps !!
sudeep   |    my blog

SQL Server  >  SQL Server Integration Services