CODEWITHSUNDEEP
t-sqlssis
Urmita Banerjee
Urmita Banerjee

Reputation: 21

Need to join oracle and sql server tables in oledb source without using linked server

My ssis package has an oledb source which joins oracle and sql server to get source data and loads it into sql server oledb destination. Earlier we were using linked server for this purpose but we cannot use linked server anymore.

So I am taking the data from sql server and want to return it to the in clause of the oracle query which i am keeping as sql command oledb source.

I tried parsing an object type variable from sql server and putting it into the in clause of oracle query in oledb source but i get error that oracle cannot have more than 1000 literals in the in statement. So basically I think I have to do something like this:

select * from oracle.db where id in (select id from sqlserver.db).

Since I cannot use linked server so i was thinking if I could have a temp table which can be used throughout the package.

I tried out another way of using merge join in ssis. but my source data set is really large and the merge join is returning fewer rows than expecetd. I am badly stuck at this point. I have tried a number if things nothung seems to be working.

Can someone please help. Any help will be greatly appreciated.

Upvotes: 1

Views: 763

Answers (1)

Eric Brandt
Eric Brandt

Reputation: 8111

A couple of options to try.

Lookup:

My first instinct was a Lookup Task, but that might not be a great solution depending on the size of your data sets, since all of the records from both tables have to pulled over the wire and stored in memory on the SSIS server. But if you were able to pull off a Merge Join, then a Lookup should also work, but it might be slow.

Set an OLE DB Source to pull the Oracle data, without the WHERE clause. Set a Lookup to pull the id column from your SQL Server table. On the General tab of the Lookup, under Specify how to handle rows with no matching entries, select Redirect rows to no-match output.

The output of the Lookup will just be the Oracle rows that found a matching row in your SQL Server query.

Working Table on the Oracle server

If you have the option of creating a table in the Oracle database, you could create a Data Flow Task to pipe the results of your SQL Server query into a working table on the Oracle box. Then, in a subsequent Data Flow, just construct your Oracle query to use that working table as a filter.

Probably follow that up with an Execute SQL Task to truncate that working table.

Although this requires write access to Oracle, it has the advantage of off-loading the heavy lifting of the query to the database machine, and only pulling the rows you care about over the wire.

Upvotes: 1

Related Questions