SSIS Package properties

Properties are nothing but natural extension of data fields. A property is a member that provides a flexible mechanism to read, write, or compute the value of a private field. These properties allow us to change basic attributes such as the package name and version, execution plan, connection manager and they also allow us to add important attributes such as passwords to execute the package.

 

Package properties divided on 8 categories.

1.       Check Points

2.       Execution

3.       Forced Execution Value

4.       Identification

5.       Misc.

6.       Security

7.       Transactions

8.       Version

We will see one by one property. Here we are describing the properties in brief. We will public very soon in all important properties in details.

Check Points

Suppose we are running the package and it got failed after completing more than 95%. Again we need to re-run this package. We need to wait to complete it. Suppose once again it got failed. It is time consuming. Let assume is any mechanism that save my executing result and when we running remaining failed package (5%), it will save the time as well as efforts. In SSIS we have a package properties know as Check points.

Checkpoints force SSIS to maintain a record of the control flow executable that have successfully run. In addition, SSIS records the current values of user-defined variables. This current context will be stored in an XML file which is defined in the package property window. After that, if a package fails during execution, SSIS will reference the checkpoint file when we are trying to rerun the package. It will first do so by retrieving the current variable values as they existed prior to package failure and, based on the last successful executable that ran, start running the package where it left off. That is, it will continue executing from the point of failure, assuming.

 

Ø  CheckpointFileName — Specify the path where the checkpoint file need to be created in XML Format. File name of the checkpoint context values. This file is deleted after a successful run of the package.

Ø  CheckpointUsage — Specifies whether checkpoints are used it means it tells us when a checkpoint file should be used

·         Always

·         Never

·         IfExists

  

Ø  SaveCheckpoints — this indicates whether the package saves checkpoints. This property must be set to True to restart a package from a point of failure.

  

Execution

   

 

Ø  DelayValidation —indicates whether package validation is delayed.

Ø  Disable —indicates whether the package is disabled.

Ø  DisableEventHandlers —specifies whether the package event handlers run.

Ø  FailPackageOnFailure — Specifies whether the package fails if an error occurs in a package task.

Ø  FailParentOnError — Specifies whether the parent container fails if an error occurs in a child container.

Ø  MaxConcurrentExecutables —the number of executable files that the package can run concurrently (-1 indicates no limit).

Ø  MaximumErrorCount —the maximum number of errors that can occur before a package stops running.

Ø  PackagePriorityClass —the Win32 thread priority class of the package threads.

Forced Execution Value

  

 

Ø  ForcedExecutionValue — If ForcedExecutionValue is set to True, this is a value that specifies the optional execution value that the package returns.

Ø  ForcedExecutionValueType — the data type of ForcedExecutionValue.

Ø  ForcedExecutionValue — a Boolean value that specifies whether or not the optional execution value of the container should be forced to contain a particular value.

Identification

In this section we see the package creation date, package creator description of the package, Id of the package (GUID) and package type.

  

Ø  CreationDate — Date the package was created.

Ø  CreatorComputerName — Computer on which the package was developed.

Ø  CreatorName — Name of the package developer.

Ø  Description — Description of the package functionality.

Ø  ID —the package GUID which is assigned when the package is created. This property is read-only.

Ø  Name — Package name.

Ø  PackageType — the package type.it may be

·         Default

·         DTS Wizard

·         DTS Designer

·         SQL Replication

·         DTS Designer 100

·         SQL DB Maint

Misc.

  

Ø  Configurations — Package configurations (only available for Package Deployment Approach).

Ø  Expressions — Create expressions for the package. Allows us to set properties based on attributes or parameters.

Ø  ForceExecutionResult —the execution result of the package. The values are

·         None

·         Success

·         Failure

·         Completion.

Ø  LocaleId — A Microsoft Win32 locale.

Ø  LoggingMode — A value that specifies the logging behavior of the package. The values are

·         Disabled

·         Enabled

·         UseParentSetting

Ø  OfflineMode — indicates whether the package is in offline mode.

Ø  SuppressConfigurationWarnings — indicates whether the warnings generated by configurations are suppressed.

Ø  UpdateObjects — indicates whether the package is updated to use newer versions of the objects it contains, if newer versions are available.

Security

ØPackagePassword —the password for package protection levels (EncryptSensitiveWithPassword and EncryptAllWithPassword) that require passwords.

Ø  ProtectionLevel —the protection level of the package. This property is used to specify how sensitive information is saved within the package and also whether or not to encrypt the package or the sensitive portions of the package. There are below protection level.

·         DontSaveSensitive

·         EncryptSensitiveWithUserKey

·         EncryptSensitiveWithPassword

·         EncryptAllWithPassword

·         EncryptAllWithUserKey

Transactions

Packages use transactions to bind the database actions that tasks perform into atomic units. By doing this, they maintain data integrity. In other words, they allow developers to group tasks together to be executed as a single transaction.

 

Ø  IsolationLevel — sets the way locking works between transactions. There is different way to implement the Isolation level in SSIS package.

·         Unspecified

·         Chaos

·         ReadUncommitted

·         RepeatableRead

·         Serializable

·         Snapshot

Ø  TransactionOption — the transactional participation of the package. The values are                 

·         Not Supported

·         Supported

·         Required.

Version

Ø  VersionBuild —the version number of the build of the package.

Ø  VersionComments — Comments about the version of the package.

Ø  VersionGUID — The GUID of the version of the package. This property is read-only.

Ø  VersionMajor— the latest major version of the package.

Ø  VersionMinor— the latest minor version of the package.