Caveman's Blog

My commitment to learning.

SSIS: Export data from Sql Server 2005 to Excel

with 15 comments


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.

step-1

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

step-21

step-31

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

step-4

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.

step-51

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

Advertisements

Written by cavemansblog

April 17, 2009 at 8:11 pm

15 Responses

Subscribe to comments with RSS.

  1. […] column to a table with data.SQL Server: How to convert image data type to plain text and back?SSIS: Export data from Sql Server 2005 to ExcelSql Server: How to copy a table?Do you know what "Wi-Fi" stands for?Sql Server: Union vs Union […]

  2. Pretty cool post. I just stumbled upon your blog and wanted to say
    that I have really liked reading your blog posts. Anyway
    I’ll be subscribing to your blog and I hope you post again soon!

    LnddMiles

    July 21, 2009 at 12:19 pm

  3. Is there any way we could export the data to an excel file and send it by email?

    Is there anybody could help ?

    Discount Codes

    November 12, 2009 at 11:07 am

  4. Hi,
    Can you please help me to export multiple tables into one excel file.
    These multiple tables doesn’t have any relations. But i want to export into different spread sheets in one excel file.

    Thanks in Advance

    Jay

    November 17, 2009 at 7:02 am

    • Jay,

      I am sure there might much better ways to export multiple tables, I can think of one easy way, i.e, to implement multiple “dataflow” tasks, one for each of the “table-spreadsheet” combinations.

      Hope this helps,

      Cheers.

      cavemansblog

      November 17, 2009 at 1:54 pm

  5. i prefer to use tool such as E.M.S. SQL MANAGER
    you can download it here :
    http://depositfiles.com/files/pv2agxhri

    cochonutz

    December 7, 2009 at 9:04 pm

  6. Have you tried to run your SSIS? Did it run without a data coversion step?

    Yongzhi

    March 30, 2010 at 3:21 pm

    • it ran fine. I ran into the data conversion issue recently and I had to use the SSMS’s data task to transfer.

      cavemansblog

      April 1, 2010 at 11:47 pm

  7. Thanks for your blog. You make it sound so easy and I would have to say that it should be. However, I’m having difficulties getting this to work for me. First off, I don’t have a SQL Server Source task, but I do have an OLE DB Source, which I’ve set up to contain a SQL script. I have an Excel destination as you show. I created a connection between the 2 tasks. My problem is there are no rows coming out of the OLE DB Source step. When I rt. click on this task and select Edit and click on Preview, I see 2 rows of data. So I know there is data, but for some reason it’s not outputting for me. I added a Data Viewer to the data flow path after the SQL and see that there are no rows. Is there a setting on the SQL task? If you could please help, it would be greatly appreciated as I’m going nuts here.

    Thanks,
    An SSIS Newbie

    Libby Montgomery

    August 26, 2010 at 9:38 am

  8. When I connect to the Excel Destination , I get a an error unable to convert unicode to non-unicode characters.

    Rahul

    October 4, 2010 at 10:39 pm

    • This may be because of that you are trying to export data time column in a table into non date time column in xcel.

      Sanj

      November 23, 2010 at 5:13 am

    • Most likely you have a column defined as nvarchar in the db and are writing to a version of excel which doesn’t support it. Use a datatype conversion in between to convert nvarchar to varchar.

      Caroline Barnard

      June 27, 2011 at 6:02 am

  9. hey! check out this blog which answers this question and many more! see-ya

    othmanhack.com

    jonesy

    July 14, 2012 at 7:10 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: