CozyRoc
Overview
Excel Task is SSIS Control Flow task for management of Excel workbooks.
Setup
Use the General page of the Excel Task Editor dialog to configure the options needed to process an Excel workbook.
Options
- Name
-
Specify task name.
- Description
-
Specify task description.
- StopOnFailure
-
Specify whether the Excel Task terminates if action fails.
- Action
-
Specify task action. This property has the options listed in the following table.
Value Description Load from stream Loads Excel workbook from stream object at runtime. Selecting this value displays the dynamic option SourceStream. Save to stream Saves Excel workbook to stream object at runtime. Selecting this value displays the dynamic options TargetStream. Get worksheets list Gets list of worksheets in Excel workbook. The result is regular array. Selecting this value displays the dynamic option ResultVariable. New worksheet Creates new worksheet. Selecting this value displays the dynamic options SaveAtEnd, IsWorksheetVariable, Worksheet, WorksheetVariable. Rename worksheet Renames worksheet. Selecting this value displays the dynamic options SaveAtEnd, IsOldNameVariable, OldName, OldNameVariable, IsNewNameVariable, NewName, NewNameVariable. Delete worksheet Deletes worksheet. Selecting this value displays the dynamic options SaveAtEnd, IsWorksheetVariable, Worksheet, WorksheetVariable. Clear worksheet Clears worksheet. Selecting this value displays the dynamic options SaveAtEnd, IsWorksheetVariable, Worksheet, WorksheetVariable. Delete Columns Deletes columns in worksheet. Selecting this value displays the dynamic options SaveAtEnd, IsWorksheetVariable, Worksheet, WorksheetVariable, IsStartColumnVariable, StartColumn, StartColumnVariable, IsCountVariable, Count, CountVariable. Delete Rows Deletes rows in worksheet. Selecting this value displays the dynamic options SaveAtEnd, IsWorksheetVariable, Worksheet, WorksheetVariable, IsStartRowVariable, StartRow, StartRowVariable, IsCountVariable, Count, CountVariable. Find cell Finds cell with specific value in worksheet. Selecting this value displays the dynamic options FoundColumnVariable, FoundRowVariable, IsWorksheetVariable, Worksheet, WorksheetVariable, IsValueVariable, Value, ValueVariable, IsStartColumnVariable, StartColumn, StartColumnVariable, IsStartRowVariable, StartRow, StartRowVariable. Replace cell Finds cell with specific value in worksheet and replaces it with another. Selecting this value displays the dynamic options SaveAtEnd, ReplaceCountVariable, IsWorksheetVariable, Worksheet, WorksheetVariable, IsOldValueVariable, OldValue, OldValueVariable, IsNewValueVariable, NewValue, NewValueVariable. Set password (1.5 SR-1) Set open or modify password for Excel workbook. Selecting this value displays the dynamic options SaveAtEnd, IsOpenPasswordVariable, OpenPassword, OpenPasswordVariable, IsModifyPasswordVariable, ModifyPassword, ModifyPasswordVariable. - SourceStream
-
Select an existing user-defined variable, or click <New variable...> to create a variable.
Related Topics: Integration Services Variables, Add Variable
- TargetStream
-
Select an existing user-defined variable, or click <New variable...> to create a variable.
Related Topics: Integration Services Variables, Add Variable
- ResultVariable
-
Select an existing user-defined variable, or click <New variable...> to create a variable.
Related Topics: Integration Services Variables, Add Variable
- SaveAtEnd
-
Specify to save workbook at the end of task execution.
- IsWorksheetVariable
-
Indicates whether the worksheet is stored in a variable. This property has the options listed in the following table.
Value Description True The worksheet is stored in a variable. Selecting the value displays the dynamic option WorksheetVariable. False The worksheet is explicitly specified. Selecting the value displays the dynamic option Worksheet.
- Worksheet
-
Specify Excel worksheet.
- WorksheetVariable
-
Select an existing user-defined variable, or click <New variable...> to create a variable.
Related Topics: Integration Services Variables, Add Variable
- IsOldNameVariable
-
Indicates whether the old worksheet name is stored in a variable. This property has the options listed in the following table.
Value Description True The old worksheet name is stored in a variable. Selecting the value displays the dynamic option OldNameVariable. False The old worksheet name is explicitly specified. Selecting the value displays the dynamic option OldName.
- OldName
-
Specify old worksheet name.
- OldNameVariable
-
Select an existing user-defined variable, or click <New variable...> to create a variable.
Related Topics: Integration Services Variables, Add Variable
- IsNewNameVariable
-
Indicates whether the new worksheet name is stored in a variable. This property has the options listed in the following table.
Value Description True The new worksheet name is stored in a variable. Selecting the value displays the dynamic option NewNameVariable. False The new worksheet name is explicitly specified. Selecting the value displays the dynamic option NewName.
- NewName
-
Specify new worksheet name.
- NewNameVariable
-
Select an existing user-defined variable, or click <New variable...> to create a variable.
Related Topics: Integration Services Variables, Add Variable
- IsStartColumnVariable
-
Indicates whether the cell start column is stored in a variable. This property has the options listed in the following table.
Value Description True The start column is stored in a variable. Selecting the value displays the dynamic option StartColumnVariable. False The start column is explicitly specified. Selecting the value displays the dynamic option StartColumn.
- StartColumn
-
Specify worksheet cell start column. The value is 1-based.
- StartColumnVariable
-
Select an existing user-defined variable, or click <New variable...> to create a variable.
Related Topics: Integration Services Variables, Add Variable
- IsStartRowVariable
-
Indicates whether the cell start row is stored in a variable. This property has the options listed in the following table.
Value Description True The start row is stored in a variable. Selecting the value displays the dynamic option StartRowVariable. False The start row is explicitly specified. Selecting the value displays the dynamic option StartRow.
- StartRow
-
Specify worksheet cell start row. The value is 1-based.
- StartRowVariable
-
Select an existing user-defined variable, or click <New variable...> to create a variable.
Related Topics: Integration Services Variables, Add Variable
- IsCountVariable
-
Indicates whether the count is stored in a variable. This property has the options listed in the following table.
Value Description True The count is stored in a variable. Selecting the value displays the dynamic option CountVariable. False The count is explicitly specified. Selecting the value displays the dynamic option Count.
- Count
-
Specify columns/rows count. Set to -1 to remove all used columns/rows after specified start position.
- CountVariable
-
Select an existing user-defined variable, or click <New variable...> to create a variable.
Related Topics: Integration Services Variables, Add Variable
- FoundColumnVariable
-
Found column result. Select an existing user-defined variable, or click <New variable...> to create a variable.
Related Topics: Integration Services Variables, Add Variable
- FoundRowVariable
-
Found row result. Select an existing user-defined variable, or click <New variable...> to create a variable.
Related Topics: Integration Services Variables, Add Variable
- IsValueVariable
-
Indicates whether the search value is stored in a variable. This property has the options listed in the following table.
Value Description True The search value is stored in a variable. Selecting the value displays the dynamic option ValueVariable. False The search value is explicitly specified. Selecting the value displays the dynamic option Value.
- Value
-
Specify search value.
- ValueVariable
-
Select an existing user-defined variable, or click <New variable...> to create a variable.
Related Topics: Integration Services Variables, Add Variable
- ReplaceCountVariable
-
Replace count result. Select an existing user-defined variable, or click <New variable...> to create a variable.
Related Topics: Integration Services Variables, Add Variable
- IsOldValueVariable
-
Indicates whether the old value is stored in a variable. This property has the options listed in the following table.
Value Description True The old value is stored in a variable. Selecting the value displays the dynamic option OldValueVariable. False The old value is explicitly specified. Selecting the value displays the dynamic option OldValue.
- OldValue
-
Specify old value to find.
- OldValueVariable
-
Select an existing user-defined variable, or click <New variable...> to create a variable.
Related Topics: Integration Services Variables, Add Variable
- IsNewValueVariable
-
Indicates whether the new value is stored in a variable. This property has the options listed in the following table.
Value Description True The new value is stored in a variable. Selecting the value displays the dynamic option NewValueVariable. False The new value is explicitly specified. Selecting the value displays the dynamic option NewValue.
- NewValue
-
Specify new value to replace with.
- NewValueVariable
-
Select an existing user-defined variable, or click <New variable...> to create a variable.
Related Topics: Integration Services Variables, Add Variable
- IsOpenPasswordVariable (1.5 SR-1)
-
Indicates whether the Excel workbook open password is stored in a variable. This property has the options listed in the following table.
Value Description True The open password is stored in a variable. Selecting the value displays the dynamic option OpenPasswordVariable. False The open password is explicitly specified. Selecting the value displays the dynamic option OpenPassword.
- OpenPassword (1.5 SR-1)
-
Specify Excel workbook open password.
- OpenPasswordVariable (1.5 SR-1)
-
Select an existing user-defined variable, or click <New variable...> to create a variable.
Related Topics: Integration Services Variables, Add Variable
- IsModifyPasswordVariable (1.5 SR-1)
-
Indicates whether the Excel workbook modify password is stored in a variable. This property has the options listed in the following table.
Value Description True The modify password is stored in a variable. Selecting the value displays the dynamic option ModifyPasswordVariable. False The modify password is explicitly specified. Selecting the value displays the dynamic option ModifyPassword.
- ModifyPassword (1.5 SR-1)
-
Specify Excel workbook modify password.
- ModifyPasswordVariable (1.5 SR-1)
-
Select an existing user-defined variable, or click <New variable...> to create a variable.
Related Topics: Integration Services Variables, Add Variable