• 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