Problems with Parameterized insert SQL with OLEDB Destination

Hello,

I've searched around and can't find any references to the problem I'm having. I'd appreciate any ideas or input.

I'm trying to use the OLEDB Destination for an insert at the end of a long data flow. I need to parameterize the input, and for some of the columns I need to use literal values instead of parameters. It seems like this should be the most common thing in the world, but I'm at a loss to get it to work.

I type in the SQL statement just like I would with an OLEDB Command transformation, with the ? character for the appropriate columns in the VALUES clause. However, when I try to use Parse Query I get this error:

"Parameter Information cannot be derived from SQL statements. Set parameter information before preparing command."

OK, so I start searching around for ways to set the parameter information. Nada. On the Mappings tab the parameter list is empty. I check MSDN and it says this:

"If you have entered a parameterized query by using ? as a parameter placeholder in the query text, use the Set Query Parameters dialog box to map query input parameters to package variables."

Set Query Parameters dialog box? I don't see this anywhere. What am I missing?

The options with the SQL Server Destination seem even more limited, as I don't see any way to use a SQL statement or stored procedure.

For the moment I'm going to stub this off with an OLEDB Command transformation with a downstream Trash desintation, but hopefully that's only going to be temporary.

Thanks,
Dan

Update: I tried using a stored procedure instead of a parameterized SQL statement, and this does not work either--though with different error info:

"Invalid parameter number"

If anyone has any idea what's going on here, I could really use the help. Is there a known bug with the OLE-DB Destination not being to handle parameterized SQL statements or stored procedure calls? Does anyone know about this mysterious "Set Query Parameters" dialog box?

Thanks,
Dan

|||You have got the wrong end of the stick with the OLE-DB destination. The SQL Command option allows you to target "results of an SQL statement", that is not the same as a parameterised command. Result set means a SELECT. To target a comand such as UPDATE or DELETE you need to use the OLE-DB Command as you did previously. The Command Tx does not require any subsequent transformations, so no need for the Trash destination.|||Thank you for your post, Darren. I appreciate your taking the time.

Knowing that OLE-DB Command transform does not require a downstream destination helps me a lot. Somewhere I had picked up the idea that all paths through the data flow needed to terminate in a destination.

That said, I still say something is very strange in all this. Here is a description of the OLE-DB Destination from MSDN/BOL:

"The OLE DB destination loads data into a variety of OLE DB-compliant databases using a database table or view or an SQL command."

Sounds about right to me. So I'm at a loss to resolve that idea with the statement in the same article, to which you referred, that the SQL option is for "The results of an SQL statement." Huh? How does calling a SELECT statement coincide with the idea of "loading date into a variety of OLE-DB compliant databases?"

This gets even more confusing: If you click through to the "OLE DB Destination Editor (Connection Manager Page)" article from the main article, you can find this explanation that the SQL command is to "Load data into the OLE DB destination by using a SQL query."

Then another contradiction comes in: the Build Query button in the OLE-DB Destination editor defaults to building a SELECT statement.

My point in all this is not to prove you wrong or anything, Darren, but rather to submit that I think we have a combination design and documentation bug here: somewhere along the line the SQL capabilities of the OLE-DB Destination got confused with the data-selecting functionality of the OLE-DB Source. Or am I missing something?

Thanks again,
Dan

|||

I certainly think the docs are not very clear. I think I know what it does, and what the capabilities of the SQL option is, but may be wrong. I have a habit of not reading Books Online when I (think I) know what it does already. I couldn't find anything particuarly strong in support of my description other than that snippet. Hopefully sombody else will give their opinion on this, but either way you should submit some documentation feedback using the link on the page.

If you are right in your expection of how it works, then feedback that the documention is not detailed enough to allow you to get it working properly. If I am right then feedback that the docs gave you the wrong impression. All these references to the docs mean Doug will be along in a minute to clarify for us! Sure he will still appreciate the feedback being logged.

|||Well, I started to post the feedback to the MSDN page, but when I tried to hit the Enter key to make a line break in my message it submitted it prematurely, and after that the feedback form was gone. Oh, well. Hopefully someone reading this thread will be able to use this information.

That said, setting aside the documentation issues, what do you think of this concept of a SQL SELECT statement with a Destination? Assuming this is not a bug, what would be the purpose for this functionality?

My theory is that this is something akin to a copy-and-paste bug, in which a feature from the OLE-DB Source was reused in the Destination. But I'm ready and willing to be wrong.

Dan

|||

I always do my feedback from within my local copy of Books Online. After the rating option this just opens an email, much easier.

You can insert data into a table. You can insert data into a view. A view is just a SELECT statement, so my take is the OLE-DB provider adds some additional functionality that pseudo materializes the SELECT. After all it just needs the meta-data and it can get this from a select as well as a table. The data is then processed into the base table. There is probably more information in the depths of the OLE-DB specifications, but I have always found life is too short for those. Why not just try it and run a profiler trace at the same time. Compare the underlying statements issued by the different methods will give you a clearer understanding of the differences, as well as the similarities.