With this Blog, Let's explore some important SSIS Package Properties with its Description:
The Picture below is a screen shot of the Properties window:
As shown in the above screenshot the Package Properties which a SSIS Developer can set can be categorized into following 8 Sections. Depending on what Executable we have selected\highlighted the Properties window options\categories\sections may vary:
1. Checkpoints: When a package fails and you want to rerun the package from the point of its failure instead of from the start then we can use checkpoints.
Following 3 are the checkpoint related properties:
Disable : We set it to true to indicate whether the executable is disabled. Default is False.
DisableEventHandlers : Specifies whether the package event handlers run. The default is False.
FailPackageOnFailure : Specifies whether the package fails if an error occurs in a package component. The only valid value of this property is False.
FailParentOnError : Specifies whether the parent container fails if an error occurs in a child container. The default value is of this property isFalse.
MaxConcurrentExecutables : The number of executable files that the package can run concurrently. The default value of this property is -1, which indicates that there is no limit.
MaximumErrorCount : The maximum number of errors that can occur before a package stops running. The default value of this property is 1.
PackagePriorityClass : Specifies the execution priority of the package. The values are
3. Forced Execution Value
Following 3 are the related properties:
ForceExecutionValue : A Boolean value that specifies whether the optional execution value of the container should be forced to contain a particular value. The default value of this property is False.
The default value of this property is Default.
5. Misc
Configurations : The collection of configurations that the package uses. We click the browse button (…) to view and configure package configurations. Package configurations enable the properties of Package object to be dynamically updated at runtime. We have to check the Enable Package configuration Checkbox and then Add.
The 5 configuration Types are :
The default value of this property is EncryptSensitiveWithUserKey.
7. Transactions : The properties in this category configure the isolation level and the transaction option of the package.
IsolationLevel : Here we can specify the isolation level of the package transaction. The values\options are:
The default value of this property is Serializable.
The system applies the IsolationLevel property to package transactions only when the value of the TransactionOption property is Required.
The value of the IsolationLevel property requested by a child container is ignored when the following conditions are true:
The value of the child container's TransactionOption property is Supported.
The child container joins the transaction of a parent container
The value of the IsolationLevel property requested by the container is respected only when the container initiates a new transaction. A container initiates a new transaction when the following conditions are true:
The value of the container's TransactionOption property is Required.
The parent has not already started a transaction.
TransactionOption : The transactional participation of the package. The values are : Not Supported, Supported and Required.
The default value of this property is supported.
8. Version :
VersionBuild : Build Number of the Package.
VersionComments : Version Comments of the Package.
VersionGUID: GUID(Globally Unique Identifier) of the Package version.
VersionMajor: Major Version of the Package.
VersionMinor: Minor Version of the Package.
Note : Thanks for Reading this blog. Please like and leave your comments. Blog is based on self study and is subject to change as and when I get something new to add\modify.
Thanks,
Paras
The Picture below is a screen shot of the Properties window:
As shown in the above screenshot the Package Properties which a SSIS Developer can set can be categorized into following 8 Sections. Depending on what Executable we have selected\highlighted the Properties window options\categories\sections may vary:
1. Checkpoints: When a package fails and you want to rerun the package from the point of its failure instead of from the start then we can use checkpoints.
Following 3 are the checkpoint related properties:
CheckpointFileName : Specify the Name with full path of a checkpoint file. Rather than using a hard-coded path, it's a good idea to use an expression that concatenates a path defined in a package variable and the package name.
CheckPointUsage: Indicates whether a package uses checkpoints. Options are Never (default), IfExists, or Always.
SaveCheckpoints : Indicates whether a package saves checkpoints. Options : True or False (default). Set to TRUE to enable checkpoint functionality.
SaveCheckpoints : Indicates whether a package saves checkpoints. Options : True or False (default). Set to TRUE to enable checkpoint functionality.
2. Execution: In this category of Package Properties we set the run time behavior of a package object.
Following 8 are the Execution related properties:
DelayValidation : We Set this to TRUE if we want to delay the execution of a Executable until runtime. Default is false.
Following 8 are the Execution related properties:
DelayValidation : We Set this to TRUE if we want to delay the execution of a Executable until runtime. Default is false.
Disable : We set it to true to indicate whether the executable is disabled. Default is False.
DisableEventHandlers : Specifies whether the package event handlers run. The default is False.
FailPackageOnFailure : Specifies whether the package fails if an error occurs in a package component. The only valid value of this property is False.
FailParentOnError : Specifies whether the parent container fails if an error occurs in a child container. The default value is of this property isFalse.
MaxConcurrentExecutables : The number of executable files that the package can run concurrently. The default value of this property is -1, which indicates that there is no limit.
MaximumErrorCount : The maximum number of errors that can occur before a package stops running. The default value of this property is 1.
PackagePriorityClass : Specifies the execution priority of the package. The values are
3. Forced Execution Value
Following 3 are the related properties:
ForcedExecutionValue : If ForceExecutionValue is set to True, a value that specifies the optional execution value that the package returns. The default value of this property is 0.
ForcedExecutionValueType : The data type of ForcedExecutionValue. The default value of this property is Int32. It may be Boolean, Byte, char, DateTime etc.
ForcedExecutionValueType : The data type of ForcedExecutionValue. The default value of this property is Int32. It may be Boolean, Byte, char, DateTime etc.
ForceExecutionValue : A Boolean value that specifies whether the optional execution value of the container should be forced to contain a particular value. The default value of this property is False.
4. Identification
CreationDate : The date that the package was created.
CreatorComputerName : Specifies the name of the computer on which the package was created
CreatorName : Specifies the domain and user name of the individual who created the package. Eg : DomainName\UserName
Description : In this section we can provide the description of the object.
ID : A read only Object GUID is assigned when the package is created. Select Generate New ID in the drop-down list to generate a new random value for the ID property.
Name : Name of the selected or highlighted object eg: Package, Foreach Loop Container, Bulk Insert Task, Data Flow Task, etc.
PackageType : The package type. The values are :
CreationDate : The date that the package was created.
CreatorComputerName : Specifies the name of the computer on which the package was created
CreatorName : Specifies the domain and user name of the individual who created the package. Eg : DomainName\UserName
Description : In this section we can provide the description of the object.
ID : A read only Object GUID is assigned when the package is created. Select Generate New ID in the drop-down list to generate a new random value for the ID property.
Name : Name of the selected or highlighted object eg: Package, Foreach Loop Container, Bulk Insert Task, Data Flow Task, etc.
PackageType : The package type. The values are :
The default value of this property is Default.
5. Misc
Configurations : The collection of configurations that the package uses. We click the browse button (…) to view and configure package configurations. Package configurations enable the properties of Package object to be dynamically updated at runtime. We have to check the Enable Package configuration Checkbox and then Add.
The 5 configuration Types are :
XML configuration File
Environment Variable
Registry Entry
Parent Package Variable
SQL Server
Expressions : You can create property expressions for all the package properties that object model includes, not just the properties listed in the Properties window. To view existing property expressions, expand Expressions. Click the browse button (…) in an expression text box to modify and evaluate an expression.
ForceExecutionResult : The execution result of the package. The values are None, Success, Failure, and Completion. The default value of this property is None
Environment Variable
Registry Entry
Parent Package Variable
SQL Server
Expressions : You can create property expressions for all the package properties that object model includes, not just the properties listed in the Properties window. To view existing property expressions, expand Expressions. Click the browse button (…) in an expression text box to modify and evaluate an expression.
ForceExecutionResult : The execution result of the package. The values are None, Success, Failure, and Completion. The default value of this property is None
LocaleId : A Microsoft Win32 locale. The default value of this property is the locale of the operating system on the local computer.
LoggingMode : A value that specifies the logging behavior of the package. The values are Disabled, Enabled, and UseParentSetting. The default value of this property is UseParentSetting.
OfflineMode : Indicates whether the package is in offline mode. This property is read-only. The property is set at the project level. Normally, SSIS Designer tries to connect to each data source used by your package to validate the metadata associated with sources and destinations. You can enable Work Offline from the SSIS menu, even before you open a package, to prevent these connection attempts and the resulting validation errors when the data sources are not available. You can also enable Work Offline to speed up operations in the designer, and disable it only when you want your package to be validated.
SuppressConfigurationWarnings : Indicates whether the warnings generated by configurations are suppressed. The default value of this property is False.
OfflineMode : Indicates whether the package is in offline mode. This property is read-only. The property is set at the project level. Normally, SSIS Designer tries to connect to each data source used by your package to validate the metadata associated with sources and destinations. You can enable Work Offline from the SSIS menu, even before you open a package, to prevent these connection attempts and the resulting validation errors when the data sources are not available. You can also enable Work Offline to speed up operations in the designer, and disable it only when you want your package to be validated.
SuppressConfigurationWarnings : Indicates whether the warnings generated by configurations are suppressed. The default value of this property is False.
UpdateObjects : Indicates whether the package is updated to use newer versions of the objects it contains, if newer versions are available. For example, if this property is set to True, a package that includes a Bulk Insert task is updated to use the newer version of the Bulk Insert task that Integration Services provides. The default value of this property is False.
6. Security : The properties in this category are used to set the protection level of the package.
PackagePassword : The password for package protection levels (EncryptSensitiveWithPassword and EncryptAllWithPassword) that require passwords. Click the browse button (…) to open this window where you can provide password.
ProtectionLevel : Here we can specify the protection mode and the protection method of the Package. The values\options are :
6. Security : The properties in this category are used to set the protection level of the package.
PackagePassword : The password for package protection levels (EncryptSensitiveWithPassword and EncryptAllWithPassword) that require passwords. Click the browse button (…) to open this window where you can provide password.
ProtectionLevel : Here we can specify the protection mode and the protection method of the Package. The values\options are :
The default value of this property is EncryptSensitiveWithUserKey.
7. Transactions : The properties in this category configure the isolation level and the transaction option of the package.
IsolationLevel : Here we can specify the isolation level of the package transaction. The values\options are:
The default value of this property is Serializable.
The system applies the IsolationLevel property to package transactions only when the value of the TransactionOption property is Required.
The value of the IsolationLevel property requested by a child container is ignored when the following conditions are true:
The value of the child container's TransactionOption property is Supported.
The child container joins the transaction of a parent container
The value of the IsolationLevel property requested by the container is respected only when the container initiates a new transaction. A container initiates a new transaction when the following conditions are true:
The value of the container's TransactionOption property is Required.
The parent has not already started a transaction.
TransactionOption : The transactional participation of the package. The values are : Not Supported, Supported and Required.
The default value of this property is supported.
8. Version :
VersionBuild : Build Number of the Package.
VersionComments : Version Comments of the Package.
VersionGUID: GUID(Globally Unique Identifier) of the Package version.
VersionMajor: Major Version of the Package.
VersionMinor: Minor Version of the Package.
Note : Thanks for Reading this blog. Please like and leave your comments. Blog is based on self study and is subject to change as and when I get something new to add\modify.
Thanks,
Paras
