SQLTeam.com | Weblogs | Forums

Data flow source - sql command VARIABLE - unable to add


I experienced the bug whereby one is unable to add parameters to sql text that one has ? (question marks) in. This seems like a well documented bug and I was unable to get around it by rearranging the sql command in various ways.
So I instead went to doing a variable (string), and using data flow source as "SQL command from variable". But then when i select my variable, of course, I end up with Error ... command text was not set for the command object". Well of course it's not, the package hasn't run yet.

how do I get around this seeming dichotomy? And yes I have the variable set to EVALUATE AS EXPRESSION. but since it's not filled yet.. ?


After 20 articles on web, I finally stumbled across the key piece that I'd not picked up anywhere else:

Second, you need to manually set a "default" value for your SQL variable
in the Variables pane, otherwise the OLE DB Source has nothing to use
for the SQL statement at design time.
(and the evaluate as expression needs to be false, not true).