Overview
The Excel Task is an SSIS Control Flow task for management of Excel workbooks.
Parameters
General
Use the General page of the Excel Task Editor dialog to configure the parameters needed to process an Excel workbook.
Specify the task name.
Specify the task description.
Specify whether or not the Excel Task should terminate if the chosen action fails.
Specify the task action. This parameter has the options listed in the following table.
Option Description Load from stream Loads an Excel workbook from a stream object at runtime. Selecting this action displays the dynamic parameter SourceStream. Save to stream Saves an Excel workbook to a stream object at runtime. Selecting this action displays the dynamic parameter TargetStream. Get worksheets list Gets a list of worksheets from an Excel workbook. The result is a regular array. Selecting this action displays the dynamic parameter ResultVariable. New worksheet Creates a new worksheet. Selecting this action displays the dynamic parameters SaveAtEnd, IsWorksheetVariable, Worksheet, WorksheetVariable. Rename worksheet Renames a worksheet. Selecting this action displays the dynamic parameters SaveAtEnd, IsOldNameVariable, OldName, OldNameVariable, IsNewNameVariable, NewName, NewNameVariable. Delete worksheet Deletes a worksheet. Selecting this action displays the dynamic parameters SaveAtEnd, IsWorksheetVariable, Worksheet, WorksheetVariable. Clear worksheet Clears a worksheet. Selecting this action displays the dynamic parameters SaveAtEnd, IsWorksheetVariable, Worksheet, WorksheetVariable. Delete Columns Deletes columns in a worksheet. Selecting this action displays the dynamic parameters SaveAtEnd, IsWorksheetVariable, Worksheet, WorksheetVariable, IsStartColumnVariable, StartColumn, StartColumnVariable, IsCountVariable, Count, CountVariable. Delete Rows Deletes rows in a worksheet. Selecting this action displays the dynamic parameters SaveAtEnd, IsWorksheetVariable, Worksheet, WorksheetVariable, IsStartRowVariable, StartRow, StartRowVariable, IsCountVariable, Count, CountVariable. Find cell Finds a cell with a specific value in a worksheet. Selecting this action displays the dynamic parameters FoundColumnVariable, FoundRowVariable, IsWorksheetVariable, Worksheet, WorksheetVariable, IsValueVariable, Value, ValueVariable, IsStartColumnVariable, StartColumn, StartColumnVariable, IsStartRowVariable, StartRow, StartRowVariable. Replace cell Finds a cell with a specific value (old value) in a worksheet and replaces it with another value (new value). Selecting this action displays the dynamic parameters SaveAtEnd, ReplaceCountVariable, IsWorksheetVariable, Worksheet, WorksheetVariable, IsOldValueVariable, OldValue, OldValueVariable, IsNewValueVariable, NewValue, NewValueVariable. Set password 1.5 SR-1 Set an open or modify password for an Excel workbook. Selecting this action displays the dynamic parameters SaveAtEnd, IsOpenPasswordVariable, OpenPassword, OpenPasswordVariable, IsModifyPasswordVariable, ModifyPassword, ModifyPasswordVariable. Get cell 1.6 SR-3 Get cell value. Selecting this action displays the dynamic parameters ResultVariable, IsWorksheetVariable, Worksheet, WorksheetVariable, IsStartColumnVariable, StartColumn, StartColumnVariable, IsStartRowVariable, StartRow, StartRowVariable. Set cell 1.6 SR-3 Set cell value. Selecting this action displays the dynamic parameters SaveAtEnd, IsWorksheetVariable, Worksheet, WorksheetVariable, IsValueVariable, Value, ValueVariable, IsStartColumnVariable, StartColumn, StartColumnVariable, IsStartRowVariable, StartRow, StartRowVariable. Set worksheet visible 1.6 SR-4 Set worksheet visibility. Selecting this action displays the dynamic parameters SaveAtEnd, IsWorksheetVariable, Worksheet, WorksheetVariable, IsValueVariable, Value, ValueVariable. Copy worksheet 1.8 Copies a worksheet. Selecting this action displays the dynamic parameters SaveAtEnd, IsWorksheetVariable, Worksheet, WorksheetVariable, IsNewNameVariable, NewName, NewNameVariable. Specify the stream object from which to load an Excel workbook at runtime. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Specify a stream object to which the Excel workbook should be saved at runtime. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Specify the variable where the list of worksheets from the specified Excel workbook should be stored as a regular array. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Indicate whether or not the workbook should be saved at the end of the execution of this task. This parameter has the options listed in the following table.
Option Description True The workbook should be saved when execution of the task is complete. False The workbook should not be saved to a file. The changes will only be kept in memory. Indicate whether or not the worksheet name is stored in a variable. This parameter has the options listed in the following table.
Option Description True The worksheet name is stored in a variable. Selecting this option displays the dynamic parameter WorksheetVariable. False The worksheet name is specified explicitly. Selecting this option displays the dynamic parameter Worksheet. Explicitly specify the worksheet name.
Specify the variable containing the worksheet name. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Indicate whether or not the old worksheet name is stored in a variable. This parameter has the options listed in the following table.
Option Description True The old worksheet name is stored in a variable. Selecting this option displays the dynamic parameter OldNameVariable. False The old worksheet name is specified explicitly. Selecting this option displays the dynamic parameter OldName. Specify the old worksheet name.
Specify the variable in which the old worksheet name is stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Indicate whether or not the new worksheet name is stored in a variable. This parameter has the options listed in the following table.
Option Description True The new worksheet name is stored in a variable. Selecting this option displays the dynamic parameter NewNameVariable. False The new worksheet name is specified explicitly. Selecting this option displays the dynamic parameter NewName. Specify the new worksheet name.
Specify the variable in which the new worksheet name is stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Indicate whether or not the start column for the specified action is stored in a variable. This parameter has the options listed in the following table.
Option Description True The start column is stored in a variable. Selecting this option displays the dynamic parameter StartColumnVariable. False The start column is specified explicitly. Selecting this option displays the dynamic parameter StartColumn. Explicitly specify the start column. The value is 1-based.
Specify the variable in which the start column is stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Indicate whether or not the start row is stored in a variable. This parameter has the options listed in the following table.
Option Description True The start row is stored in a variable. Selecting this option displays the dynamic parameter StartRowVariable. False The start row is specified explicitly. Selecting this option displays the dynamic parameter StartRow. Explicitly specify the start row. The value is 1-based.
Specify the variable in which the start row is stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Indicate whether or not the row or column count is stored in a variable. This parameter has the options listed in the following table.
Option Description True The row or column count is stored in a variable. Selecting this option displays the dynamic parameter CountVariable. False The row or column count is specified explicitly. Selecting this option displays the dynamic parameter Count. Explicitly specify the number of rows or columns to be deleted. Set the count to -1 to remove all used columns or rows after the specified start position.
Specify the variable containing the number of rows or columns to be deleted. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Indicate the variable where the column of the cell that was found will be stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Indicate the variable where the row of the cell that was found will be stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Indicate whether or not the value is stored in a variable. This parameter has the options listed in the following table.
Option Description True The value is stored in a variable. Selecting this option displays the dynamic parameter ValueVariable. False The value is specified explicitly. Selecting this option displays the dynamic parameter Value. Explicitly specify the value.
Specify the variable in which the value is stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Indicate the variable where the resulting number of replacements is stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Indicate whether or not the old value (i.e. the value being replaced) is stored in a variable. This parameter has the options listed in the following table.
Option Description True The old value is stored in a variable. Selecting this option displays the dynamic parameter OldValueVariable. False The old value is specified explicitly. Selecting this option displays the dynamic parameter OldValue. Explicitly specify the old value to be found and replaced.
Specify the variable where the old value (the one being found and replaced) is stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Indicate whether or not the new value is stored in a variable. This parameter has the options listed in the following table.
Option Description True The new value is stored in a variable. Selecting this option displays the dynamic parameter NewValueVariable. False The new value is specified explicitly. Selecting this option displays the dynamic parameter NewValue. Explicitly specify the new value which will replace the old value.
Specify the variable containing the new value which will replace the old value. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Indicate whether or not the open password for the Excel workbook is stored in a variable. This parameter has the options listed in the following table.
Option Description True The open password is stored in a variable. Selecting this option displays the dynamic parameter OpenPasswordVariable. False The open password is specified explicitly. Selecting this option displays the dynamic parameter OpenPassword. Explicitly specify the open password for the Excel workbook.
Specify the variable containing the open password for the Excel workbook. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Indicate whether or not the modify password for the Excel workbook is stored in a variable. This parameter has the options listed in the following table.
Option Description True The modify password is stored in a variable. Selecting this option displays the dynamic parameter ModifyPasswordVariable. False The modify password is specified explicitly. Selecting this option displays the dynamic parameter ModifyPassword. Explicitly specify the modify password for the Excel workbook.
Specify the variable containing the modify password for the Excel workbook. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Knowledge Base
- Where can I find the documentation for the Excel Task?
- How can I get the name of a worksheet from an Excel file and rename it to a known name?
Samples
What's New
- New: A new 'Copy worksheet' action.
- Fixed: 'Find Cell' action returned incorrect result (Thank you, John).
- New: A new ‘Set worksheet visible’ action.
- New: A new 'Get cell' action.
- New: A new 'Set cell' action.
- New: A new 'Set password' action.
- New: Introduced task.
Related documentation
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.