Excel Task

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

Name

Specify the task name.

Description

Specify the task description.

StopOnFailure

Specify whether or not the Excel Task should terminate if the chosen action fails.

Action

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.
SourceStream

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

TargetStream

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

ResultVariable

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

SaveAtEnd

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.
IsWorksheetVariable

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.
Worksheet

Explicitly specify the worksheet name.

WorksheetVariable

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

IsOldNameVariable

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.
OldName

Specify the old worksheet name.

OldNameVariable

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

IsNewNameVariable

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.
NewName

Specify the new worksheet name.

NewNameVariable

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

IsStartColumnVariable

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.
StartColumn

Explicitly specify the start column. The value is 1-based.

StartColumnVariable

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

IsStartRowVariable

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.
StartRow

Explicitly specify the start row. The value is 1-based.

StartRowVariable

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

IsCountVariable

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.
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.

CountVariable

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

FoundColumnVariable

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

FoundRowVariable

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

IsValueVariable

Indicate whether or not the search value is stored in a variable. This parameter has the options listed in the following table.

Option Description
True The search value is stored in a variable. Selecting this option displays the dynamic parameter ValueVariable.
False The search value is specified explicitly. Selecting this option displays the dynamic parameter Value.
Value

Explicitly specify the search value.

ValueVariable

Specify the variable in which the search 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

ReplaceCountVariable

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

IsOldValueVariable

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.
OldValue

Explicitly specify the old value to be found and replaced.

OldValueVariable

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

IsNewValueVariable

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.
NewValue

Explicitly specify the new value which will replace the old value.

NewValueVariable

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

IsOpenPasswordVariable (1.5 SR-1)

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.
OpenPassword (1.5 SR-1)

Explicitly specify the open password for the Excel workbook.

OpenPasswordVariable (1.5 SR-1)

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

IsModifyPasswordVariable (1.5 SR-1)

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.
ModifyPassword (1.5 SR-1)

Explicitly specify the modify password for the Excel workbook.

ModifyPasswordVariable (1.5 SR-1)

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