Filtering data from heterogenous sources

[rickj65]

I have a table in an Oracle database and I need to copy a filtered set of records from that table into a SQL Server database table on a monthly basis.

The problem I'm having is that the WHERE condition that I use for filtering the Oracle table can change on a month-to-month basis based on data found in a table in the SQL Server database. For example, let's say I keep a table of vendors in a SQL Server table. This list of vendors changes each month. I want to download a list of products from a master products table in my Oracle database that pertain to the list of vendors.

How can I dynamically generate a SQL statement to run a data pump task against the Oracle database? Or is there a different way I can accomplish this?

Thanks.

 

[rickj65]

TWIMC,

I solved my problem above by writing an ActiveX Script Task that creates the SQL string that I use in querying the Oracle database.

Within that script I dynamically build an "IN" list of vendors..."WHERE VendorID IN ('ABC', '123', 'DEF', etc.)" that I built by creating an ADO connection to the Vendors table which I loop through.

After building the entire SQL statement, I apply the SQL dynamically to the Source of the data pump task each time the package is run. Thanks to sqldts.com for explaining this here...

http://www.sqldts.com/default.aspx?6,102,213,0,1

It works like a charm.

An alternative I could've used would be to create a Linked Server in SQL Server to the Oracle database and join the tables I need in one query. However, many people warned me about joining tables across heterogenous data sources.