1. Open SQL Server Enterprise Manager
2. Click on the + to expand the tree for the database server.
You will need to expand it three times.
Note: If the SQL Server does not appear here, you
will need to right click and go through the Wizard
for SQL Server Registration.
- The first level of the tree is called Microsoft SQL Server,
The second level of the tree is called SQL Server Group,
and the third level of the tree is your SQL Server Database.
For these instructions we will call our SQL Server Database,
'local'.
3. Under the local Database, click on the + to expand the tree control
for 'Data Transformation Services'
4. Under Data Transformation Services, right click and select "New Package"
5. A DTS Package screen will appear. Drop a 'Other Connection' onto the workspace.
a. Select the Oracle in ODBC Driver from the Data Source list.
b. Select your DSN in the User/System DSN pull down list.
c. In the Username field type in the userid.
d. In the Password filed type in the password.
e. Click on the OK button.
6. In the DTS Package screen. Drop an Excel 8.0 Connection onto the workspace.
a. In the File Name click on the ... button and navigate to an excel file. Click Open to open the file.
Then click on the OK button in the Connection Properties screen.
7.In the DTS Package screen, create a Transform between the Other Connection and Excel by
clicking on the Other Connection icon, hold down the control key and click on the Excel icon.
Right click and select Transform Data Task.
8. Right-click on the Transform Data Task Link and click properties. This is where the application is causing a performance problem. Since it is retrieving a list of available tables, synonyms, views, etc from the Oracle Database.
Partager