Tags: 
1.5, Task, Excel

Overview

The Excel Task is an SSIS Control Flow task for management of Excel workbooks.


Setup

Use the General page of the Excel Task Editor dialog to configure the parameters needed to process an Excel workbook.

Parameters

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.
Set worksheet visible 1.6 SR-4 Set worksheet visibility. Selecting this action displays the dynamic parameters SaveAtEnd, IsWorksheetVariable, Worksheet, WorksheetVariable, IsValueVariable, Value, ValueVariable.

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


Samples


Related documentation