OLE DB Command with property expression

Hi,

I am trying to use an OLE DB Command to run a different SQL command for each row in the data flow. I have a script component that builds the SQL command and puts it in a data flow variable, and a property expression in the data flow mapping [OLE DB Command].[Sql Command] to that variable.

The problem is that the OLE DB Command has a validation error, saying that "the command text was not set for the command object", and it doesn't run.

Did I miss anything? should I tell the OLE DB Command that the SQL command would come from expression? or is it a bug?

Thanks.

This validation error can be ignored if you set DataFlow task's DelayValidation to true. However, if you don't set parameters correctly, you may get parameters not bound error later on.

The way I do this is to set up a "dummy" OLEDBCommand first, with the good parameter binding(s), then during execution, the SqlCommand will be replaced by the my real expression value at runtime time - This will work under the assumption that the column metadata does not change overtime, which means, although the SqlCommand will change at runtime, the parameter bindings will remain the same.

Pls try it out and let me know if you have further questions.

Thanks

Wenyang

|||

Thanks.

Unfortunately, my sql commands differ in metadata. I can set it so that the parameters are in the same order for all commands, but some of the commands will not use all the parameters.

anyway, I tried what you suggested with a specific command, but it still doesn't work. the SQL command is deleted whenever I save the package (before running it), and then I get the same validation error.

Isn't there a straight forward solution? I mean, the package knows I set a property expression, otherwise it wouldn't delete the SQL command upon save. If so, why does it through the error? is it a bug?

|||

. It is by design the column metadata has to be the same for each SqlCommand expression value. This is the case not only for OLE DB Command, but also for other dataflow components when using expressions in similar conditions.

. I tried once again, as long as the expression is set correctly, the SqlCommand's value will be set to the expression evaluation result after saving my pkg(before execution). To me there is no bug here. Which version you are on? Did you set "DelayValidation" to true? Can you try again and make sure you set your expression at DataFlow task's "expression" property properly?

Thanks

Wenyang

|||

Thanks, you helped me find (part of) the problem.

I had the sql command set (using a property expression) to a variable that gets its value only during the data flow execution from a script component. the default value for the variable was empty, and when I saved the package it put the empty value into SqlCommand, which is not a valid value.

Setting DelayValidation to true didn't help here, since at the beginning of the data flow execution the variable is still empty, and I get the same validation error at runtime.

What did help is putting a dummy default value to the variable. now it is running without validation errors.

but...

it doesn't change the property of the OLE DB command :-(

the variable gets a different value for each processed row (I check it with a script), but the OLE DB command still runs the default value assigned to it at the beginning.

any ideas?

|||

Your scenario should work as well. Please provide the SqlServer version you are on and the detailed steps of how you set up the expression for OleDbCommand's SqlCommand property and I'll see how I can help.

Thanks

Wenyang