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: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.
Pages: 1 2