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

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.



In the Select Source Table and Views wizard screen one needs to select the AdventureWorks.HumanResources.Employee Table in the Source and by default Employee will be visible in the Destination. Thereafter click on the Edit Mappings… and make sure all the data types are identical to the one shown in the below image and apply the changes:





In the Save and Execute Package wizard screen there are two options namely Execute Immediately and Save SSIS Package as file system. The developer can make either of the choices and then click on the Finish button to run and end the package configuration.

Conclusion
The SSIS Package will export the data to Employee Sheet of SampleData.xlsx file.