Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SSIS and Excel 2007

By Dinesh Asanka,

Introduction

Importing Excel files into SQL Server database is a common task needs to carry out by the DBAs and developers. Also, in case of data warehouse, you need to extract data from various data sources. Most of the times, Excel is one of the data sources.

As you are aware, importing and exporting data from and to the Excel is simple. it is just a matter of drag and drop few data flow controls and configuring them according to your need.

Importing Excel 2007 File

If you are asked to import an Excel file, you can use Excel Source from the Data Flow Sources in SQL Server Integration Services (SSIS) and select correct version from the available list.

You can see that you can only import Excel files up to Microsoft Excel 97-2005 version, which means that you are not allow to import Excel 2007 files from the above control.

However, if you follow below steps, you can import Excel 2007 files into the SQL Server.

1. Drag and drop OLE DB Source data flow source to the data flow task.

2. Double click the OLE DB Source and click New button for OLE DB Connection Manager.

3. Click New button in the Configure OLE DB Connection Manager screen.

4. Select Native OLE DB\Microsoft Office 12.0 Access Database Engine OLE DB Provider from the OLE DB Provider list.

5. Select All option and at the Extended Properties enter Excel 12.0. After this you will see a screen like following image.

You can see the selected provider at the top of the screen.

6. Enter the file name with full path and make sure you have the extension xlsx.

7. After clicking OK button, you will be taken to the initial screen, in which you have to select the worksheet you want.

Exporting to Excel 2007 File

There is no different when exporting to Excel file. it is again you have to modify the destination connection as above.

SQL Server 2008

Though things are difficult in SQL Server 2005, things have become easy with SQL Server 2008. In SQL Server 2008, you simply need to select the Excel 2007 from the drop down.

Like import, you can use same way to export data to Excel 2007 by using Excel destination in SQL Server 2008.

Total article views: 14134 | Views in the last 30 days: 30
 
Related Articles
FORUM

excel source to import data gets some values as null

using an excel source to import data gets some values as null

FORUM

import data from excel file to sql server database

import data from excel file to sql server database

FORUM

Problem while importing Data from Excel to SQL Server 2005

Problem while importing Data from Excel to SQL Server 2005

BLOG

SQL Server – Import Data from Excel using T-SQL

To import data from an Excel file to SQL Server you can use SQL Server Import and Export Wizard. You...

FORUM

import data from excel file to sql server database

import data from excel file to sql server database

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones