Data Flow Task in SSIS, and Oracle Schema

Hi Guys,

I have a question regarding SSIS and Oracle

Our company has two major versions of products, and currently, I am
working on this project to migrate data from version A of the product
to version B of the product. The database schemas for two versions
have huge differences, therefore we chose to use SSIS to migrate the
data.



We have a number of clients that are on Version 1, and we want to
write one code base to migrate all clients' data from Version 1 to
Version 2. And they are all on Oracle.


In the SSIS, for each package I have two connections, ConnectionOne
connects to the Oracle database for Version1, and ConnectionTwo
connects to Version2.



In a Dataflow task, I use an OLE DB Source to get data from
ConnectionOne, and then, depending on the tables, I may do some
transformations or lookups and etc, then I dump the data to a OLE DB
Destination to ConnectionTwo.



Here is the problem, in an OLE DB Source or OLE DB Destination, if I
use a "Table or view" as the data access mode, then the name of the
table or the view is something like "ClientSchema"."Table_Name", where
ClientSchema is the schema name in Oracle, and "Table_Name" is the
name of the table.



So, as you can see, our different clients will have different
"ClientSchema", so, for different clients, we have to change each Data
Flow task in the SSIS package, pretty much like a complete re-work.



Is there a way that we can only reference the table name in the OLE DB
Source/Destination, such as just "Table_Name"? If we can do this, then
when we migrate different clients' database, all we need to do is:
change the connection string in the 2 Connection Managers.







Thank you very much!



Wenbiao

.

Relevant Pages

  • Re: Installing 32-bit and 64-bit oracle client on windows 2003 Server
    ... Windows services to connect to the oracle database server hosted on HP- ... if you want to install 2 different clients you need to install ... and hit the documentation button. ...
    (comp.databases.oracle.server)
  • Re: Data Flow Task in SSIS, and Oracle Schema
    ... Perhaps you could use a varible as the destination name ... We have a number of clients that are on Version 1, ... In the SSIS, for each package I have two connections, ConnectionOne ... then I dump the data to a OLE DB ...
    (microsoft.public.sqlserver.dts)
  • Re: (484 lines) The "CEO-as-crook" FAQ
    ... And, from some past writings of his, he's not any less of an asshole ... R&D at Oracle had been in decline before the dot com era; ... at Oracle or by working for one of its partners a/o clients. ... and Oracle, successful consultants. ...
    (sci.research.careers)
  • Re: Installing 32-bit and 64-bit oracle client on windows 2003 Server
    ... Windows services to connect to the oracle database server hosted on HP- ... if you want to install 2 different clients you need to install ... The oracle documentation does not cover how to run two clients on ...
    (comp.databases.oracle.server)
  • Re: Can One Query an Oracle 10 Data Base under FreeBSD?
    ... I believe OP is looking for assurances that the oracle 7 and 8 clients ... We are going to be querying a Pinnacle server and ... Only the initial retrieval uses the oracle data base, ...
    (freebsd-questions)