Derived column based on result of Oracle query

Hi,

I need to create a derived column for each row in a SQL dataset.

This derived column needs to be created by passing across two values from the SQL dataset and querying an Oracle table based on those parameters. If the Oracle query returns a record(s) then the derived column should be set to 1 otherwise leave it as default (0).

One of these parameters needs to check a date range so I can't use a Lookup Transformation...any ideas how I can accomplish this ?

Thanks

Here is one way to do this...

-Have one source for the sql source and another source for the oracle source.

-Send them both to a merge join, choose a left outer join with the sql source as the left input, choose one column from the oracle source.

-After the merge join use a derived column transformation. Use an expression to say that if the field from the oracle source is null then 0 else 1.

edit: After re-reading your post, the date range makes things much more difficult. It may be best to send both sources to staging tables in sql server, and then write run a query to join them together perhaps a (correlated subquery with a case statement in the select clause).

|||

Hi,

I am not sure if this will solve your problem completely:

In a Data Flow Task pull the two values from the SQL dataset into the Recordset Destination.

Now, in the Control Flow Task, use a For Each Loop Container to store the Recordset into a variable (you will have to create variables for each field that you want to write to the destination as well as one for the recordset).

Loop through the recordset and for each pair of values, use a Excecute SQL Task to execute a SQL query or a Stored Procedure which will return matching record(s). Now based on whether a record was returned or not, write all the fields, plus the derived column value into the destination.

Regards,

B@.ns

|||

Thanks guys - I actually used a combination of your techniques to get the result.

|||I just wanted to point out that you can use a Lookup to do this. You just need to go the the advanced tab, enable memory restriction, and you can alter the query used for the lookup to include ranges.sql