1

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.

Improve this question

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Hi Eric, thank you for your help. I am using lookup and it works fine but there is an issue. So my oracle source is a query. When i run that query using the linked server i get more rows than the number of rows that is flowing out of the oledb source. So I am using Ado net source and ado net destination but that is taking so long. Do you by any chance know why all the rows is not returned by oeldb source?? Thank you!!!
Unfortunately, I don't have any insights on your row count problem. I've heard of it happening, but I haven't experienced it so I've never dug into it.
Hey Eric, there is another issue so the oracle query is looking up id values of a sql server table and only matching rows should be passed. But it is trying to send all the sql server lookup id col values which has nulls corresponding to the oracle id's it does not have and error is coming.
Hey, so this issue was happening coz of the Ignore Failure option. I changed it to Redirect rows to no match output. Now i get only the matched rows. Thanks for your help!
Ack. Sorry about that! I’ll update the answer shortly. Glad you got it working.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.