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

  • Question

  • Hi there,

    I'm trying to dynamically create select statements in my package. I'm using expressions in exactly the way you'd expect i.e. the Expression on the Vaiable is set to this:

    "SELECT synchDel,synchRec,pcode,pavailable,pprice,pdcode,pocode FROM "+@[Template::DBName]+".property WHERE pcode=''FAK080032''"

    Which means the Value on the Variable evaluates as:

    SELECT synchDel,synchRec,pcode,pavailable,pprice,pdcode,pocode FROM abnrps.property WHERE pcode=''FAK080032''


    or alternatively, sometimes I want to run

    "SELECT synchDel,synchRec,pcode,pavailable,pprice,pdcode,pocode FROM "+@[Template::DBName]+".property WHERE synchrec >  DATE_ADD(NOW(),INTERVAL -1 HOUR)"

    which evalutes as

    SELECT synchDel,synchRec,pcode,pavailable,pprice,pdcode,pocode FROM abnrps.property WHERE synchrec >  2010-01-19 12:05


    Point being, I want to be able to choose which query I run during package execution


    Now I want to store those Expressions in in database table, and read them into the package at run time. My idea for doing this was to run a simple Execute SQL task that returns the Expression string in the ResultSet, and stores it in a vaiable. Later on in the package, I use that Variable as a datasource in a dataflow task (I know that you cannot dynamically configure number of columns etc - it is just the WHERE that changes between expressions)

    However, I'm having a problem doing this, it appears that the Resultset of the Execute SQL taks populates the VALUE of the Variable. I need to to populate the EXPRESSION on the Variable. So, even though, the variable is set to EvaluateAsExpression = True, it is actually getting its value set, not its expression, and therefore can't evaluate properly.

    I can kinda prove this, becuase if I store the expression in [SSIS Configurations], and bring it in through a configuration, it gives me the option to configure \Package.Variables[Template::SelectQuery2].Properties[Expression], which works great. (Configuring this won't work as a proper solution though, becuase I need more flexibility to decide which SELECT I need during package execution). I am thinking the Execute SQL task uses the resultset to populate \Package.Variables[Template::SelectQuery2].Properties[Value], which doesn't work.

    So, a few questions: Does what I'm describing above make sense, or am I missing somethign obvious? WHat other ways do I have to dynamically set the Expression property of a variable? What ways are you guys doing this sort of thing?
    Tuesday, January 19, 2010 1:55 PM

Answers

  • This is not possible as if you set the value of expression to another variable and expect the expression to be evaluated its not possible.
    What you can do instead is split ur query into 3 parts:

    "SELECT synchDel,synchRec,pcode,pavailable,pprice,pdcode,pocode FROM "
    @[Template::DBName]
    ".property
    WHERE pcode=''FAK080032''"

    now save 1st & last in a separate column.
    and build ur query as:
    @[User::Qry1] + @[Template::DBName] + @[User::Qry2]
    Hope this helps !!
    Sudeep   |    My Blog
    • Proposed as answer by Todd McDermid Tuesday, January 19, 2010 4:58 PM
    • Marked as answer by Zongqing Li Monday, January 25, 2010 7:41 AM
    Tuesday, January 19, 2010 2:06 PM