Alternate Title: Copying an Oracle 10G XE Table to SQL 2005
Using VS 2005 and SSIS
Data Transformation Services (DTS) which came with MS SQL
Server 7.0 has now emerged as SQL Server Integration Services in MS SQL 2005
Server, an enterprise quality ETL (extraction, transformation, and load)
program. DTS as implemented in SQL 2000 was limited in its tasks and transformations.
The graphic user interface gave the package the required functionality by integrating
workflow process with the integrate dataflow. The Control and Script support
by VBA were also limited.
SSIS is now a full blown service offering a complete
solution featuring extensibility, a .Net interface to designing packages, a
great number of tasks, transformations, logic, security, management, and
improved deployment. As you will see in this tutorial, you will encounter a
multitude of wizards to get you to the intended goal. However, going from DTS
to SSIS for designing packages requires skills that may take time to get used
to. This tutorial is written in a minimalist style so that you will not be
taken through hyper jumps unless that is absolutely needed.
Scope of this tutorial
Using the VS 2005 IDE a Business Intelligence Project will
be created. Using an Integration Services Project template, a single table
from the Oracle 10G XE database will be copied to a database on the SQL 2005
Server which will be created by an SSIS task. This step-by-step tutorial shows
you how this may be achieved.
The Oracle 10G XE server, the MS SQL2005 server and the
Visual Studio 2005 are all on the same machine running on the XP Professional
Media Center Edition operating system.
Figure 1 shows the "Departments" table on the HR
database in Oracle 10G XE that will be copied to the SsisEditor
database created for this exercise on the MS SQL 2005 Server. This table has 27
rows and 4 columns.
Figure 1
