How to Import Data from Microsoft Office Excel 2007 to SQL Server 2005

SSIS Developers often come across a scenario where they need to import data from a Microsoft Office Excel 2007 Spreadsheet. This article provides a step by step guide on how to import data from a Microsoft Office Excel 2007 Spreadsheet to a SQL Server 2005 database.

The first step is to go to Start and select Run and type CMD and then type DTSWIZARD in the command prompt. The Welcome to SQL Server Import and Export Wizard screen will pop up on your screen as shown in the image below: 

Once you click the Next button it will take you to the Choose a Data Source wizard screen. One needs to select the Data Source as Microsoft Office 12.0 Access Database Engine OLE DB Provider and then click the Properties… button on the wizard screen which will pop up the Data Link Properties screen. Under the All tab, double click the Data Source property valve and enter the location of the Spreadsheet eg. “C:Excel2007ImportSampleData.xlsx” as the name and path of the Microsoft Office Excel 2007 file from where the data needs to be imported from. Then double click on Extended Properties and select Excel 12.0 as the property value.

The connection to the Microsoft Office Excel 2007 can be tested by clicking on the Test Connection button as shown in the image below:


Pages: 1 2


No comments yet... Be the first to leave a reply!