Help on how to design this SSIS package

[lifeforest]

Hi all,

I need to select some data from a remote database, in the where clause i have a subquery which defines the select criteria. The subquery is from another remote database.

SELECT db1.field1, db1.field2
FROM remotedb1 db1
WHERE db1.field3 IN (SELECT db2.field4
FROM remotedb2 db2)

What's the best way to design a SSIS package to get the job done?

Thanks!

 

[williey]

write a stored procedure for the query and call it in the source database.


------------------------------------------
There are 10 kinds of people in this world. One that understands binary and the other one that does not.

 

[lifeforest]

Thanks Williey. But the reality is I only have read permission on both remote databases. More suggestions?

 

[lifeforest]

Okay, now i can simplify my questions as below:

I need to design a conditional split, the value of the checking field should be in a range, which would be an array variable.

I can now get the dynamic array variable, but just dont know how to make the condition checking.

Thanks for any thoughts!!

 

[MDXer]

Use a Data Source which will contain your Main Select Statement.

Lookup Task that contains The select statements from your where Clause. Add db2.Field4 to the dataflow. Configure the lookup task to ignore failures.

Add a conditional split task that contains the login IsNull(db2.Feild4) == False Label this output as Rows to Process and then utilize this dataflow for your next step.

You can use an 80/20 lookup technique if your db2 data is large. Depending on the datatype of field4 you can hold a large amount of data without issue.

If you need the 80/20 let me know.