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

 
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:Excel2007ImportSampleData.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.