Parameters in an OLE DB Command

Hello!

Is there a way to use an OLE DB Command transform to execute the following SQL query?

"Select sum(length(" + @.[User::varDBName] + ":informix.cyh_t.u_obj)) from " + @.[User::varDBName] + ":informix.cyh_t, " + @.[User::varDBName] + ":informix.cch_t
where " +
@.[User::varDBName] + ":informix.cch_t.d_obj = {D '" + @.[User::strQueryDate] + "'} and " +
@.[User::varDBName] + ":informix.cch_t.n_objid = " + @.[User::varDBName] + ":informix.cyh_t.n_objid"

I get a DB number from an OLE DB Source, do a lookup to get the DB name and then I want to use the OLE DB command to get a result from the query but I can't seem to get this to work. Am I using the wrong transform?

Thanks.

Not quite sure what you want to do here but issuing a SELECT statement from an OLE DB Command won't do anything. It won't insert records into the pipeline for you. If that is what you want to do then LOOKUP is more suitable.

-Jamie

|||Are you querying the same database for all of your information? Or are you going at multiple databases? You might be better off doing some of this work in the control flow if you have different databases. If they are the same databases, then just issue a compound query.|||

Thank you both for your replies. I think I have a flaw with my flow. I am querying multiple databaes so I'll try reworking the flow.

Thanks again!

Parameters in a OleDb Command Transformation

Hi there,

In order to prevent lookup errors in a lookup transformation, I've decided to go for an OleDb Command Transformation.

This transformation should check the lookup and, if it turns out to be null, ir returns a dummy value. Otherwise, it would return the lookup value.

This should be done by doing something like this:

select coalesce( (select ID_Table2 from ID_Table2 where FK_Table1 = ?), 0)

suposing Table2 has an atribute called "FK_Table1" that should match a column in the data flow.

Now, such command result in this message:

"An OLE DB record is available. Source "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax eror, permission violation, or other nonspecific error".

But, it I remove the coalesce and type the following command:

select ID_Table2 from ID_Table2 where FK_Table1 = ?

It presents me no errors and allows me to continue.

Did i did anything wrong or is this something that is not possible to be done?

I know i have the option to use a script task to do this operation, but that would turn the maintenance process a little more difficult.

Otherwise, i know i could also re-direct the error from the lookup transformation and handle it. Though, my package has about 10 lookups and that would turn my package a lot more complex than

Thanks in advance

Best Regards

Andr Santana

You do know that the OLE DB Command will do a row by row search in the dataflow, and that it will be much slower than using a cached lookup, right?

As for your coalesce statement, I don't think the OLE DB command can handle parameters inside a subquery.|||

Andre,

You have more than one option to return a dummy value if the lookup fails. Here is one:

1. Configure the lookup component error output to 'ignore failure'; the place a derived column after the lookup to replace the nulls in the lookup column by the dummy value

As Phil says, OLE DB command will perform the operation for every row then the performance is worse than using Lookup transform

|||

Hello again,

Thank you Phil and Rafael for the quick reply...

This solves my problem... I didn't know I could "return" a dummy that way...

And it also improves the overall performance.

Thanks for the help

Andr