Dynamically using different schema names in Oracle

I want to access some Views in Oracle on machine <M01> with a user <user01>.
The views belong to different schemas e.g. user01.view01 and user02.view02.

When I transport my package to another machine <M02> I am facing a different situation:
The viewnames remain the same but the schemas have changed, e.g. user05.view01
and user06.view02.

I have tried to parametrize my Source SQL query but it is restricted to use parameters in the
WHERE-clause and not in the FROM-clause where I would place something like
SELECT *
FROM ?.view01

The main problem here are the differences between machine M01 and M02!
How would you handle this?
Hm, I think the best would be using an Expression in the DataFlow Source task wherein I use a
package variable. The expression would be something like:

"SELECT * FROM " + @.[SchemaName01] + ".view01"

The value of the package variable would be saved in a configuration. The configuration can be machine-dependant so the package doesn't need to be re-compiled.

The only disadvantage is, that I have plenty of SELECT statements which are stored in expressions. Not quite comfortable...

If someone has a better idea I would be graetful.
Fridtjof
|||I haven't found any practical solution.
I believe that this could be a common problem. Any suggestion on this?
|||

Your solution of using expressions sounds like the best way to go for sure. As you have observed this means you may have alot of expressions to handle but is this really so much of a problem?

-Jamie

|||Well I have plenty of SELECTs in my Oracle Data Sources. As you will know, it is not very comfortable developing SQL-Statements and then transforming them into expressions and vice-versa it's even worse.

By the way: I can imagine that one can meet this situation with an SQL Server having different
Schemas although I didn't see that so far.

Fridtjof
|||

Friedel wrote:

As you will know, it is not very comfortable developing SQL-Statements and then transforming them into expressions and vice-versa it's even worse.

I completely agree. What I always do is construct the expression elsewhere. I usually use the expression editor attached to the package's Description property. This is the safest bet as it won't do any real damage in case you press the OK button instead of the Cancel button after building the expression.

Once you have a working expression you can copy/paste it into the Expression property of your variable.

Its far from perfect I know, but it works!

By the way, I know it doesn't help you now but you'll be pleased to know that SP1 will provide an expression editor for the Expression property of a variable.

-Jamie