Home > Excel, SSIS > Export SQL Data to Excel – SSIS

Export SQL Data to Excel – SSIS


We have got so many data formats to store data in digital world. Excel is one of the most famous format for data processing and storage. In this blog post I am going to use SSIS to export SQL data to Excel file.

Implementation

Step 1: Create a SSIS package. Add a data flow task to the control flow environment

Step 2: Go to Data flow designer and add ADO.net source task

pic1Create a new connection to connect the ADO source with AdventureWorks database

pic1Select a table from the database

pic1Step 3: Add Excel destination task and connect ADO.net source with it.

pic1Configure Excel destination now. To do so, edit the Excel Destination Task and create new Excel connection.

pic1Select the location and excel file name to create the destination file. Also, select the excel version and check the first row as column name. The column names are generated from the source table.

pic1Create a new sheet with SQL table structure

pic2Select Sheet name from the drop down list

pic2Map the columns between source and excel destination task

pic2Click OK. We have done the settings.

Step 4: Execute the package.

pic2Note: This package is running on 32 bit mode.

To setup package execute mode, go to the package properties and set the Run64BitRuntime property value to false.

pic2Conclusion

This is a simple package that exports data from SQL table to Excel file.

Advertisements
  1. ike
    June 2, 2014 at 2:05 pm

    thanks for the post. how can we get a dynamic excel file with timestamp at destination.

    • June 5, 2014 at 1:38 pm

      The excel file creation show in this post is just one time task. We cannot create excel file directly.

      But, you can use the existing file to load the data if the sheet name and columns are mapping in the package.

      To answer your question, you need to have the excel file with timestamp in system and provide the excel destination task a variable that contains the dynamic file name.

  1. No trackbacks yet.

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: