How to import data from Ms Office Excel 2007 to SQL Server 2005

SSIS developers often need to import data from a Microsoft Office Excel 2007 spreadsheet. This article provides step-by-step instructions on how to import data from a Microsoft Office Excel 2007 spreadsheet into a SQL Server 2005 database.

The first step is to click Start and select Run and enter cmd and then enter Dtswizard at the command prompt. The welcome interface for the SQL Server Import and Export Wizard is displayed as shown in the following illustration:

When you click the Next button, it goes to the Select Data Source Wizard interface. Users should select a data source for Microsoft Office 12.0 Access Database Engine OLE DB Provider then click Properties in the wizard interface ... button, which pops up the Data Link property interface. On all tabs, double-click the Data Source property value and enter the location of the spreadsheet, such as the Microsoft Office Excel 2007 file name and path for the data source of the imported data, such as "C:\Excel2007\Import\SampleData.xlsx". Then double-click the extended attribute and select Excel 12.0 as the property value.