I'm having a strange SSIS (SQL Server 208 R2) issue using OPENQUERY. I have created a Linked Server to our Oracle DB and it works great. I created a view that has a JOIN using an OPENQUERY statement that uses that Linked server, and that view also works just fine when queried in Mgmt Studio.
I then tried creating an SSIS package that has an OLEDB source control that queries the view, but it fails with an ORA-01403 error when I try to Preview or run the package. The really odd thing is that if I take the SQL from the View definition and paste it into the OLEDB sourse as a SQL statement (instead of trying to use the view), the Preview and the package runs just fine.
So, it appears that the issue is that accessing a view that has a JOIN to a table using OPENQUERY inside of it doesn't seem to work.
Has anyone seen this scenario before?