SSIS: Export data from Sql Server 2005 to Excel

Exporting data from a SQL Server 2005 to MS-Excel using SQL Server Integration Services (SSIS) is one of the simple ways of data transfer. This article demonstrates a step-by-step procedure of exporting data from Microsoft SQL Server to Microsoft Excel worksheets.

Update: 06/22/09 – You can also also check out my blog on Exporting data to Excel with out using SSIS now.

1. Open Visual Studio 2005 IDE and create a new Integration Services Project.

2. Add a Data Flow Task item to the Control Flow pane and double click on it.

3. Add a OLE DB Source item to the Data Flow pane and configure it to your Source Sql Server 2005 database.

4. Add an Excel Destination item to the Data Flow pane.

5. Create a connection between the two items by dragging the green arrow on the source item and dropping it on the destination item.

6. Configure the Excel Destination Item so that it points to the destination Excel file, then make sure that the mappings between the source table and the destination table are correct.

7. Compile and execute the package to export the data.

Note: This kind of exporting will work only when you have a pre-formated (with data columns) spread-sheet in the Excel file. Make sure that the Excel file in this context is always closed while exporting data.

Following are some other ways of exporting to an Excel file:

• Use Export wizard from Sql Server 2005.
• Use distributed queries [1]
• Write a C#/VB.Net program. [2]
• etc

References:
1. SQL SERVER – 2005 – Export Data From SQL Server 2005 to Microsoft Excel Datasheet. – Pinal Dave
2. Data Transfer from SQL Server to Excel – Levent Camlibel
3. MSDN Online

---

Possibly related posts: (automatically generated)

• SQL Server: Export data to Excel