PROBLEM W/ OLE DB COMMAND

In a data I'm trying to implement an Ole DB Command to do a database table update. When I enter my sql command I get an error saying [OLE DB COMMAND [5926]] - unable to retrieve destination columns... It appears it doesn't recognize the table I'm trying to update, however I do have a sql task in the control flow that is successfully selecting a row from the same table that I'm trying to update (just to prove that I can access that table). I'm using the same connection mgr in the OLE DB as I used in the Sql task

Any suggestions?

thanks

It sounds like a problem in the way you have configured the OLE DB Command component.

What is the SQL statement that you have in there?

-Jamie

|||What is the database you are working with? Maybe the OLEDB driver doesn't provide metadata the way SSIS like it... So a SQL command in the control flow works fine (since it doesn't need to have the metadata, in the control flow it doesn't matter at all) but in the data flow metadata is essential and when the driver has a problem with it then it might be a problem...|||

My sql is

Update Person set Flag = 1 where name = ?

DB Oracle 9i

The sql in the control flow (using a sql task that is configured to use the same connection mgr) is not the same statement but's it's a select against a different table in the same schema - that works fine.

|||

I think the meta data may be the problem - I found that it does recognize the DB table since I get an oracle error when I type in an incorrect column name and that error goes away when I correct the column name and get this one:

the OLE db command [6061] unable to retrieve destination column descriptions

Has anyone had success doing a table 'update' against Oracle using the OLE DB command? if so what type of connection did you use?

thanks

|||

Tom

Unfortunately there is no easy way for OLEDBCommand transform to build up column metadata for an OLEDB Oracle connection since the provider does not support derive parameter info.

The only way to do make it work is to manually add "External Columns" info one by one at OLEDBCommand. For example for your case you need to go to "Input and Output Properties", find the "External Columns" and add a column of "name", fill in name, DataType, codepage, length etc manually there for "name", according to the correspondent input column's information.

Thanks

Wenyang