The "Excel Destination" allows users to connect to a new or existing Excel file and write data to it.
Excel Destination is available for SQL versions 2012 and higher.
• Excel Connection Manager - Users can create a new or select an existing connection to an Excel worksheet. For more information, see the Excel Connection Manager.
• Choose Worksheet - Excel users can create many worksheets within the same file. Users can select which worksheet to load data to.
• Existing Sheet Data - Users can choose from the following:
o Clear - Clears all existing and residual data before writing to the spreadsheet.
o Append - Adds data after pre-existing data in the spreadsheet. Previous data will remain in the file/worksheet.
o Overwrite - Overwrites the existing data but keeps residual data. (Example: If 100 rows are inserted into a spreadsheet with 200 rows, rows 1-100 will be overwritten and rows 101-200 will remain.)
• Row Start - Determines the row number to begin writing.
• Column Start - Determines the column number to begin writing.
• Create Table - Select this option to add table formatting.
• Table Name - Users can define the name of the table.
• Create Table Auto Filters - Select to create auto filters.
• Excel Column Mapping - Maps the column to the excel file's cell.
• Excel Columns - Users can edit or rename a column that is written to the Excel file.
• Include In Spreadsheet - This option is selected by default. Clear this option to prevent the column from being output to the Excel file.
• Style - Columns can be formatted using styles within Excel.
• Refresh Excel Columns - Refreshes the available columns that can be selected in the Excel Column Mapping property using data from the destination spreadsheet. If no columns currently exist, you will be shown the letter reference of the excel column (e.g. A, B, C, etc.)
Please see the Error Row Handling page for more information about this functionality.