Step 1
======
Create an Excel file like this, starting from A1. Note that there are five fields. The fifth field does not have any
values except for one row.
Col1 Col2 Col3 Col4 Col5
111 aaa 12/1/2006 111aaa
222 bbb 12/1/2006 222bbb
333 ccc 12/1/2006 333ccc
444 ddd 12/1/2006 444ddd
555 eee 12/2/2006 555eee nullvalues
666 fff 12/2/2006 666fff
777 ggg 12/2/2006 777ggg
888 hhh 12/2/2006 888hhh
999 iii 12/2/2006 999iii
Save the file as MyExcelFile2
Step 2
======
Start -> All Programs -> Microsoft SQL Server -> Enterprise Manager
Open up your server in the left pane of Enterprise Manager and select Data Transformation Services
Right click on Local Packages and select New package
Step 3
======
Drop a "Microsoft OLE DB Provider for SQL Server" connection object onto the designer.
Change the "New Connection:" value to DBConn
Select your Server: I am selecting [local]
Select your Database: I am selecting JambuDB
Click on OK
Step 4
======
Drop a "Microsoft Excel 97-2000" connection object onto the designer.
Change the "New Connection:" value to ExcelConn
For the FileName: option selec the Excel file you created in Step 1.
This is what I am selecting: C:\whatever\MyExcelFile2.xls
Click on Ok
Step 5
======
Click on "Transform Data Task" and first click on the ExcelConn and then on the DBConn
You should see an arrow point to DBConn from ExcelConn
Double click the arrow and the change the Description: to ExcelToSQLServer2000Task on te Source tab
On the Destination tab click on Create button and you should see something like this
CREATE TABLE [Sheet1$] (
[Col1] float NULL,
[Col2] nvarchar (255) NULL,
[Col3] smalldatetime NULL,
[Col4] nvarchar (255) NULL,
[Col5] nvarchar (255) NULL )
Change the table name if you wish. For this demo, I am leaving as is.
Click on OK
Click on OK
Step 6
======
That's it. Execute the package and you should the data in your database now.
Hope it helps.
PS: This particular activity should not have any difference in SSIS. Even if it is there, if we can do in 2000, we can
easily do it in SSIS.
jambu