Site sponsored by:

[ http://jventures.com/advertising/a2.aspx?ZoneID=4

Home

Latest Articles

Filtered Indexes in SQL Server 2008

Importance of Database Backups and Recovery Plan

Data Compression in SQL Server 2008

Latest FAQ's

ALTER TABLE SWITCH statement failed because the object '%.*ls' is not ...

ALTER TABLE SWITCH statement failed because column '%.*ls' at ordinal %d ...

ALTER TABLE SWITCH statement failed because table '%.*ls' has %d columns ...

SQL Server Reporting Server (SSRS) service is failing to start ...

Latest Software Reviews

Spotlight on ApexSQL Doc 2008

ApexSQL Enforce

Embarcadero Change Manager

SQL Server DBA Dashboard

All Articles

Performance Tuning

Audit

Business Intelligence

Clustering

Reporting Services

Developer

General DBA

ASP.NET / ADO.NET

[image] Write for Us

Share you SQL Server knowledge with others and raise your profile in the community More...

articles >> business intelligence >> How to Export Data to Microsoft Office ...

How to Export Data to Microsoft Office Excel 2007 Using SQL Server 2005

By : Ashish Kumar Mehta
Jun 18, 2008

SSIS DevelopersÂ often come across a scenario where they need to export data to aÂ Microsoft Office Excel 2007 spreadsheet.Â This article provides a step by step guide on how to export data to Microsoft Office Excel 2007.

The first step is to go to StartÂ | RunÂ | CMD and type DTSWIZARD. 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 where the Data Source needs to be selected as SQL Native Client, as data needs to be imported from SQL Server 2005. Thereafter one needs to select the Server Name from where the data needs to be imported and the appropriate Authentication Mode followed by the Database Name.

In this example SSIS Package we will be connecting to local the SQL Server Instance using window authentication and the database used will be AdventureWorks.Â

Â
In the next Choose a Destination wizard screen one needs to select Microsoft Office 12.0 Access Database Engine OLE DB Provider and then click the Propertiesâ€¦ button on the wizard screen which will pop up a Data Link Properties screen. Under the All tab, double click the Data Source property valve and mention â€œC:\Excel2007\Import\SampleData.xlsxâ€ as the name and path of Microsoft Office Excel 2007 file where data needs to be imported. Then double click on Extended Properties and mention Excel 12.0 as its property value. The SampleData.xlsx file needs to be created in the specified folder location before it can be used as a destination file.Â Â

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

Â
In the Specify Table Copy or Query wizard screen select the Copy data from one or more tables or views option and continue with the wizard to the next screen.

Next Page>>

[ http://jventures.com/advertising/a2.aspx?ZoneID=3

[ http://www.jventures.com/advertising/ssptext.htm ]

Article Topics

How to Export Data to Microsoft Office Excel 2007 Using SQL Server 2005