Command text was not set for the command object. (SQL command from variable)

If you use the OLE DB Source, OLE connection manager, and Data access mode of “SQL command from variable”, you might get the following error:

Command text was not set for the command object.

What I’m guessing that you are trying to do is place TSQL in a table, use an Execute SQL Task to fetch your SELECT statement, place the string in a variable via the “Result Set” tab of the task, and finally, send the variable to an OLE source to run.  This isn’t a bad way to code SSIS packages at all, really; at work we have a package that queries six sources, and we use this type of setup successfully. In ours each of the six queries has the same column names and number of columns, the differences in the queries are only in the WHERE clause. This therefore allow us to use/reuse one package for retrieving data from all sources.  Normally a process calls the package to run all six in a linear fashion, but the beauty of ours is that it allows any one of them to be called at any time of the day as well; we simply send a parameter to fetch the TSQL for the one that we wish to run, and we’re gold.  Nice!

So what’s up with this error?  What’s happening is that there is no “metadata”, so to speak, for the package. While the error says the command text is missing, it really isn’t – what is missing is the metadata for the transformation. It doesn’t know the columns to map, so it errors out.  What you need to do is to “seed” a query inside of the value of the variable that you are trying to execute. This query is only a placeholder and won’t be executed unless the source from your Exec SQL task fails to retrieve code, or you run it locally.

Notice in the figure that I appended a —Seed query: not used comment. This will not get executed and only serves to gen my metadata.  Notice now when I go to my OLE source, the string shows up, where before I got the error above. (We use a Script Task to parse out the comment, which gives us a “debug mode” to make sure we’re executing the correct SQL String. While debugging we may put the string to a Message Box).

Again, this “seed query” wont execute. What will is the query string that I get from my table.  If I go back to the Control Flow, I get this:

One interesting item – if I remove my seed string, here’s what I get:

But if I turn on the DelayValidation property (set to True) it works.  If however, I change to a table as a source and not the variable, the “metadata” goes away, and I have to plug my seed string back in the variable value property once again when I switch back to the SQL command from variable. Bottom line:  just leave the string in there, it shouldn’t bother you.  If you like you can do a WHERE 1=0 to only get the column names.

Thanks much,

Lee Everest

------------------------------

I gotta wake my ass up at six AM every day of this week and drag myself up to Las Colinas. Yeah, I'm doing the drywall up there at the new McDonalds.