Due: 2002-09-20 Friday 8.30 a.m.
PURPOSE:
The
purpose of this lab is to learn about
A)
Creating an ODBC DSN (Data Source Name).
B) Logging
into, and changing your SQL Server account password.
C)
Importing data from MS-Excel into SQL Server database using SQL Server’s DTS (Data
Transformation Services) facility.
[NOTE: In
order to do this lab, you must use the black colored (not others) Dell
workstations in PAI 5.38 Elements lab.]
DESCRIPTION/IMPLEMENTATION:
Consider the suppliers-parts-projects database, sample data for which is in the attached Excel spreadsheet shown in the link below. There are 4 worksheets containing relevant data in this spreadsheet:
1) SUPPLIER (SID, Name, Status, City)
2) PART (PID, Name, Color, Weight, City)
3) PROJECT (JID, Name, City)
4) SHIPMENT (SID, PID, JID, Qty)
Suppliers (S), parts (P), and projects (J) are uniquely identified by supplier id (SID), part id (PID), and project id (JID), respectively. The significance of an SPJ (shipment) row is that the specified supplier supplies the specified part to the specified project in the specified quantity (and the combination SID-PID-JID uniquely identifies such a row).
The objective of this lab is to import these 4 Excel worksheets, into 4 tables in a SQL Server database. In order to achieve this, you will download the excel spreadsheet (shown below), and use
i) The SQL Server Enterprise Manager, which comes with SQL Server client installation, and use its DTS facility.
In order to create an ODBC DSN, you will need the following:
A) ODBC Administrator software (already installed in the lab machines)
B) ODBC Driver for SQL Server software, which comes with SQL Server client installation (already installed in the lab machines)
C) A Data Source name (something that you will remember) (for discussion purposes, I will call it “Lab1DSN”).
D) Your SQL Server user id (emailed to you by TA)
E) Your SQL Server password (emailed to you by TA)
F) Your SQL Server Name (or IP Address) (Name is “UTCSDBMS”, or you can use the IP address 128.83.143.141)
How to create an ODBC DSN is described in Step 1 of “Useful Link # 10” on the class web page. To learn more about ODBC and SQL Server, read article on “Useful Link # 6” on the class web page.
Note that an ODBC DSN is not required for the rest of this lab, but will be useful for future labs.
The TA has assigned each student in the class a user account and a database in SQL Server. Use this information to connect to SQL Server and change your password, by following step 1 of “Useful Link # 9” on the class web page.
After connecting to SQL Server via Enterprise Manager, click on the “Databases” folder on the left pane. Expand your particular database that the TA has created for you. From the main menu, click on
“Tools”à”Data Transformation Services”à”Import Data…”
In the DTS Import/Export Wizard, the second window/screen allows you to “Choose a Data Source”.
From the “Data Source” drop-down, choose “Microsoft Excel 97-2000”, and enter the path+file name of the .xls file that you want to use as a source for your data, in the “file name” edit control.
Click “Next”, which brings you to “Choose a destination”.
In the “Destination” drop-down, choose “Microsoft ODBC Driver for SQL Server”
For “Server”, choose 128.83.143.141 (or UTCSDBMS).
Click on the radio-button “Use SQL Server Authentication”
Enter your SQL Server user id and password.
Select the appropriate database (from the “Database” drop-down), which was created for you by the TA.
Click “Next”, which brings you to “Specify Table Copy or Query” window.
Highlight “Copy Table(s) and View(s) from the source database”, and click “Next”.
In this window, click on the “Select All” button.
Click on each of the 4 destination table name and remove the “$” from the name.
Click on the “Transform” button, beside the PART table, and click on the cell that says, “float”.
Change it to “Numeric” with a precision of 9, and a scale of 2. Hit OK.
Click on “Next”, which brings you to “Save, Schedule, and replicate package”
Click on “Run Immediately” and click “Next”.
Look at the summary. Click “Finish”.
At this point, 4 new tables are created in your SQL Server database. To view them,
From the main menu, click on
“Action”à”Refresh”
On the left-hand pane, click on TABLES under the appropriate destination database. You should be able to see the 4 tables on the right-hand pane. Right-click each one of them to see if the data looks right. Right clicking on the table name allows you to do many more things that you can explore (including dropping the tables altogether, in case you need to start again).
Suppliers-Parts-Projects Excel Spreadsheet [.zip file]
Last Updated: 9/12/2002 8:11:12 AM