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

Parameters for Lookup Transformation Query!

Hi Gurus,

I have a Dataflow Task which has an OLE DB Source calling a SP with parameters (?, ?). Then this OLE DB Source is conencted to a Lookup Transform which also calls a SP but on a different database. I am unable to figure out how to pass parameters in a Look up Transform.

In the 'Use Results of an SQL Query' pane of Lookup Transform:

Code Snippet

EXEC GetMonthlyDataExtract 4, 2007

( I am passing month and year values) this works ok.

But when I chage to

Code Snippet

EXEC GetMonthlyDataExtract ?, ?

It says EXEC not supported. Also I can not figure out how to configure parameters since 'Reference Table' Tab of the Lookup Transform does not have any option where we can attach variables to parameters.

Also I am interested to map parameters to variables not to input columns.

If mention if that is not possible or any other alternative.

Your help will be appreciated.

Thanks,

Paraclete

It's a bit of a workaround, but I think you could put your variables in data columns and then use the Lookup in partially cached mode. Go over to the Advanced tab, check Enable Memory Restriction, Enable Caching, and Modify SQL Statement. Put your parameterized EXEC in the statment and set the parameters based on your variable/columns. Instead of executing your procedure in the PreExecute phase, now it will get executed on the first row. Since you're caching the results, the procedure should never get executed again. So it's effectively the same functionality.

Update:
On second thought, the Lookup workaround described here probably won't work. When the Lookup is in partial cache mode, it doesn't perform its own matching, but relies on the modified query and the database engine to perform the matching. Since the parameters to your procedure are not the columns you want to match on, the Lookup won't return the correct matching row.

You may also consider using the Merge Join instead of Lookup.

Parameters - Dropdown list value to lookup values and pass to sp

I have a simple report where I want to have a dropdown list box that is
populated from a sql query (no problems here).
Then I want to take the value from the selected parameter to lookup another
value in a query, then pass the returned value to a stored procedure as a
parameter.
So far, I have not been successful, does anybody have a clue how to do this?
Thanks, MikeMichael,
You will need 2 datasets (2 parameters) and 2 Report Parameters (Report
Menu). First parameter (that you already have) will fetch one set of list. In
the third (main query that will fetch the reporting data) will be queried
like below:
"
Select Count(AccountNumber) as Dis, PrimaryCareProviderId ,
PrimaryCareProviderName,InpatientServiceName,Left(DateName(Month,Dischargedatetime),3) as Mon
From dbo.Portal_PrvVolumes Where Dischargedatetime >= '07/01/2004' and
dischargedatetime < '12/01/2004'
and
primarycareproviderid = @.PrimaryCareProviderId (FIRST PARAMETER)
and InpatientServiceName = @.PrvService (SECOND PARAMETER)
"
Then create the second dataset for second parameter list.
Now create 2 report parameters...
I hope this helps...
"Michael Morisoli" wrote:
> I have a simple report where I want to have a dropdown list box that is
> populated from a sql query (no problems here).
>
> Then I want to take the value from the selected parameter to lookup another
> value in a query, then pass the returned value to a stored procedure as a
> parameter.
>
>
> So far, I have not been successful, does anybody have a clue how to do this?
>
> Thanks, Mike
>
>
>