SSIS tutorial: writing headerless data to Excel at a specific starting cell

Tutorials SSIS Tutorials
,
1

Introduction

By default, Excel Destination writes a header row with column names before the data. If you need to load data into Excel without that header row, for example, to append to a template that already has its own headers or starts at a specific cell, you can turn it off using the Headerless property in the Advanced Editor. This article explains how to configure it, including how to write to a specific starting cell.

Prerequisites

Steps

  1. Drag an Excel Destination onto the Data Flow and connect your source (e.g., OLE DB Source) to it.

  2. Create or select a ZS-Excel Connection in Connection Managers, pointing to your target Excel file.

    image
    image656×533 22.9 KB

  3. Right-click the Excel Destination, select your Excel connection, and choose Show Advanced Editor.

  4. Go to the Component Properties tab and set the following under Custom Properties:

    • Headerless: True, this removes the column name row from the output.
    • Overwrite: True (or False, depending on whether you want to replace the existing file or write into it as-is).
    • RangeStartCell: the cell where the data should start, for example B4.
    • TableName: the sheet to write to, for example $first_sheet$ or (or a specific sheet/named Sheet1).
      image
      image725×666 32.4 KB

  5. Go to the Input Columns tab and select the fields you want to write to Excel, or select all if you want every column from the source.

  6. Click OK to save the configuration.

    image
    image725×626 25.6 KB

  7. Run the package and open the Excel file to confirm the data starts at the specified cell, with no header row.

    image
    image241×223 7.85 KB

Conclusion

Setting Headerless to True in the Excel Destination’s Advanced Editor removes the column-name row from the output, and combining it with RangeStartCell lets you write data starting at any specific cell, such as B4, instead of the top-left corner of the sheet.

Still need help?

If you need assistance configuring Excel Destination to write without headers or at a specific cell, please get in touch with our support team: