Due: 2003-09-12 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:
Before
starting this lab, you will need to know the following:
a)
Elements
lab user id and password. This can only be obtained by physically going to the
lab and swiping your UT ID card. You will need this id to login to the machines
in the lab.
b)
Name
of the SQLServer machine or its IP address (given to you in class)
c)
Your
SQLServer user id (given to you in class) and password. Note: this is different
from the lab user id and password.
d)
Your SQLServer database name (given to you in
class)
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 (part C) 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 (DSN) (some name that you will remember) (for discussion purposes, I will call it “Lab1DSN”).
D) Your SQL Server user id (as given in class)
E) Your SQL Server password (as given in class)
F) Your SQL Server Name (or IP Address) (as given in class)
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.
For each student in the class, a user account and a database is created 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 the ip address or the name of the sql server
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), the name of which was given to you.
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. If everything looks correct, 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/10/2003 9:24:15 AM