The basic steps consist of configuring the Control Flow and
the Data Flow pages in the designer.
·
The control flow configuration starts by first creating a table
with the same schema as the Oracle 10 XE's "Departments"
table using an SQL statement. Once this SQL procedure is created and executed,
the table will be created (with no data) in the SQL 2005 Server's SsisEditor database.
·
The Data Flow is configured by configuring the OLE DB Editors for
the two servers. However, for completing the configuration, all information
including the tables on the two databases must be specified. And unless the
empty table with the same structure as the oracle database is present in the
SQL 2005 Server, the specification will not be met.
·
In order to complete the package, the Control Flow task is
executed to create the table in the SQL 2005 Server and then the design of the Data Flow Task is completed. In order not to complicate
the procedure this method has been used in the course of this tutorial.
Here is list of preparatory items that are needed for this
project to succeed.
·
Both SQL Server 2005 and Oracle 10G XE should be functioning
correctly.
·
SQL Server 2005 should be properly configured and tested.
·
Authentication and permissions should be in place for the objects
accessed.
·
An Instance of VS 2005 should be available.
·
SQL Server 2005 being the destination, the database to which the
table will be brought in should be in place by creating an empty database named
SsisEditor.
·
The TSQL Script to create a table in SQL 2005 server, which is a
copy of the table from
Oracle 10G XE, should be available or capable of being created in the IDE.
For both Oracle and SQL 2005 server, OLEDB providers will be
used.
