SSIS Export Excel File Task
 PreviousNext

SSIS Export Excel File Task is your one stop solution to create excel reports/workbooks from SSIS. You don't need any drivers, no need to worry about metadata issue you face with native SSIS Excel Task. We worked hard to take care most painful tasks for exporting data from any relational source to Excel.

Download SSIS PowerPack

Content

Video Tutorial

Coming Soon...

Step-By-Step

In this tutorial we will learn how to extract data into Excel from SQL Server Tables using ZS Export Excel File Task with just few clicks.
  1. Firstly, You need to Download and Install SSIS ZappySys PowerPack.
  2. Once you finished first step, Open Visual Studio and Create New SSIS Package Project.
  3. In visual studio just Drag and Drop ZS Export Excel Task in design surface from SSIS Toolbox.
    SSIS Export to Excel File Task - Drag and Drop
  4. Now, We need OLE DB Connection For Source.

How to create OLE DB Connection.

  1. Let's, Right click on Connection Managers Panel to Create OLE DB Connection, so you can use Source and Context Menu will appear, Select New OLE DB Connection from the Context Menu.
    SSIS Export to Excel File Task - Connection
  2. Now, click on New Button for create Connection.
    SSIS Export to Excel File Task - Connection
  3. Let's Configure Connection Manager, just Follow steps one by one as we have created.
    SSIS Export to Excel File Task - Connection
  4. Click OK to save connection configure setting UI.

How to load data into Excel from SQL Server using Export Excel File Task.

  1. Double click on ZS Export Excel Task for Configure it.
  2. Let's, Generate Excel files for selected tables/views. Set Source Connection we have created, click on Select for Select Source Table you want to export.
    SSIS Export to Excel File Task - Generate Excel files for selected tables/views
  3. Now, set Target Path and Other Options, Select Target Location from Browse Button. Check on Overwrite target file(s) if you want it. You can Check on Compress file to *.gz format.
    SSIS Export Excel File Task - Select Target Path and other options
  4. Click OK to save Export Excel File Task setting UI.
  5. That's all, Just Execute SSIS Export Excel File Task and Check Execution Results.
    SSIS Export Excel File Task - Execution Log

How to split Data into multiple files using Export Excel File Task.

  1. Double click on ZS Export Excel Task for Configure it.
  2. In the Split Options Tab, check on Enable Split By Size/Rows and Click on Split By Rows.
    SSIS Export Excel File Task - Split Excel Data into Multiple files
  3. Click OK to save Export Excel File Task setting UI.
  4. That's all, Just Execute SSIS Export Excel File Task and Check Execution Results.
    SSIS Export to Excel File Task - Split Options - ByRows Execute

How to split Data into groups on workbook tab rather than new file using Export Excel File Task.

  1. Double click on ZS Export Excel Task for Configure it.
  2. In the Split Options Tab, check on Enable Split By Size/Rows and Click on Split By Rows. If you specify Group By Column then new file will be created for each unique value of Group By Column (e.g. If you specify Group By Column = Country then one file will be created for each Country).
    SSIS Export Excel File Task - Split Excel Data into Multiple files
  3. Now, In the Excel Options Tab, You can change Excel File Version and set Open and Edit Password if you want to keep secure file.
    SSIS Export Excel File Task - Excel Date Formatting, Encoding Options
  4. Click OK to save Export Excel File Task setting UI.
  5. That's all, Just Execute SSIS Export Excel File Task and Check Execution Results.
    SSIS Export to Excel File Task - Split Options - ByRows Execute

Properties

Property Name Description
ExcelSheetName Sheet name of excel workbook where you want to export data
ExcelOffset Excel cell location from where you want start writing data. (e.g. If you wish to export data at 3rd row of 2nd column then use B3). Default is A1 means first row first column
ExcelRangeToClearBeforeWrite Range you want to clear before writing data (Example: A1:M25). This is helpful when you writing data on existing Sheet and you want to make sure no previous content left in specified area before writing.
ExcelVersion File version for excel. Default value is 2007 (e.g. xlsx format of Excel 2007) which supports more than 65000 rows and many new features. If you need backward compatibility for some reason then use 2003 format. Valid options are 2003, 2007, 2010 or 2013
ExcelOpenPassword Sets the password for open excel file action. So any timeuser open the file it will prompt for password.
ExcelModifyPassword Sets the password for edit excel file action. So any time user tries to save the file it will prompt for password.
SplitOnTab When Groupby option specified along with this option then data is split on tabs rather than separate file for each group. e.g. if you set Groupby=> [Country] along with SplitOnTab then you will see tab for each country in excel workbook.
PrintLandscape Printer mode to landscape when exporting file. Only applicable to Html. Pdf, Excel files
PrintPaperSize Printer paper size (e.g. A4, Letter, Legal). Only applicable to Html. Pdf, Excel files
Timeout Time out for source query. 0 means no timeout
Connection Connection for source table/query from which you want to export data. This can be valid ADO.net or OLEDB connection Manager Name or ID
Source Source for the data. This can be table(s), query, view(s) or proc. If its other than table or view then you must specify --proc or --query parameter to indicate access mode. You may list multiple objects by separating them using vertical bar (e.g table1|table2|view1). You can also use pattern for objects (e.g. cust%|prod%)
Schema Schema name or pattern to search for objects when object name contains pattern (e.g. Cust%)
ExcludeViews If pattern used on object name then do not include matching views in the objects to export.
ExcludeTables If pattern used on object name then do not include matching views in the objects to export.
UseMappingFile Use custom layout and additional processing instructions per column
MappingAccessMode AccessMode for Mapping file. This determines how mapping information will be read at runtime
MappingDirectValue Mapping information in the format of XML
MappingVariable Variable which holds mapping information
MappingFilePathVariable Variable which holds mapping file path
MappingFilePath Mapping file path which contains custom layout and additional information
Target Target file or folder path
Append Append data at the end of file
Overwrite Overwrite target file if exists
GroupByColumn Split files by group by column value. e.g. if you group by Country field then you will have one file for each country. Country name will be used as file name suffix. If you specify --split option then you may have more than one file for each value in group by field depending on split size/rows you specify
Compress Compress file in gzip format
KeepRaw Keep source file after compressing it when --gzip option enabled. By default source file is deleted once its compressed.
SplitFiles Split into multiple files (when split enabled you can split by filesize or rowcount. Check --splitsize and --splitrows options).
MaxSizePerFile Maximum size per file. You can specify in bytes,kb,mb or gb. Example: 10mb, 5000kb, 1gb, 2000bytes
MaxRowsPerFile Maximum rows per file
MaxRowsToExport Maximum number of rows to export.
LoggingMode LoggingMode determines how much information is logged during Package Execution. Set Logging mode to Debugging for maximum log.

Setting UI

SSIS Export to Excel File Task - Drag and Drop
SSIS Export to Excel File Task - Drag and Drop

See Also

Articles / Tutorials

Click here to see all articles for [SSIS Excel Export Task] category
SSIS Data Load SQL Server to FTP/SFTP (Split Files, GZip)

SSIS Data Load SQL Server to FTP/SFTP (Split Files, GZip)

Introduction In this blog post you will see how easy it is to load large amount of data from SQL Server to FTP/SFTP. SSIS Data Load SQL Server to FTP/SFTP (Split Files, GZip). For demo purpose we will use SQL Server as relational source but you can use same steps for any database engine […]

Create Excel Report in SSIS using a template (Export Formatted xlsx File)

Create Excel Report in SSIS using a template (Export Formatted xlsx File)

Introduction In our previous post we saw how to create excel dynamically in SSIS. Now let’s advance further and find out how to create Excel reports in SSIS using Powerful Template engine (introduced in PowerPack 2.7.4)‚ ‚ In this post you will learn how to use ZappySys Export Excel Task to export data from Multiple SQL Tables […]

SSIS export to excel dynamically (supports multiple tables)

SSIS export to excel dynamically (supports multiple tables)

Limitation of SSIS Excel Source/Destination SSIS comes with out of the box support for read/write to Excel. But its very restrictive if you want to make things dynamic because any metadata inside DataFlow cannot be changed at runtime. Here are few problems using native Excel Source or Destination. Metadata cannot be changed at runtime Datatype […]

Copyrights reserved. ZappySys LLC.