SSIS Package Properties

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:

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.

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.

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.

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 :

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

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.

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 :

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

Just a simple JOIN Scenario in SQL Server...

Create Table tblLeft
(
Col1 int,
Col2 int
)

Insert into tblLeft Values(1,1)
Insert into tblLeft Values(1,1)

Create Table tblRight
(
Col3 int,
Col4 int
)

Insert into tblRight Values(1,1)
Insert into tblRight Values(1,1)

Select * from tblLeft
Select * from tblRight

Select * from tblLeft inner join tblRight  on tblLeft.Col1 = tblRight.Col3

Select * from tblLeft left join tblRight on tblLeft.Col1 = tblRight.Col3

Select * from tblLeft right join tblRight on tblLeft.Col1 = tblRight.Col3

Truncate Vs Delete...Can Truncate be Rolled back in SQL Server?

Lets begin with some straight QUESTIONS???

We have with us...A TestTable with TestID Column.

Use this code to prepare this set up:

--Create a TestTable with TestID Column
CREATE TABLE TestTable (TestID INT)

--Insert some records in this Table
INSERT INTO TestTable Values(101),(102),(103),(104),(105)

--Run select to crosscheck 
SELECT * FROM TestTable
GO

We have TestTable with following records now:

TestID
101
102
103
104
105

Now...What will be the output of these three T-Sql Statements when run independently ?

Scenario 1:

-- Begin The Transaction
BEGIN TRAN
--Delete statement
DELETE from TestTable
-- Rollback Transaction
ROLLBACK TRAN
GO
-- Check output after Delete
SELECT * FROM TestTable
GO

Scenario 2: 

BEGIN TRAN
--Truncate statement
TRUNCATE Table TestTable
-- Rollback Transaction
ROLLBACK TRAN
GO
-- Check output after Truncate
SELECT * FROM TestTable
GO

Scenario 3:

BEGIN TRAN
--DROP statement
DROP Table TestTable
-- Rollback Transaction
ROLLBACK TRAN
GO
-- Check output after DROP
SELECT * FROM TestTable
GO

Now let me put some doubts in your mind before you answer...

1. Truncate and Drop are DDL statement. Are they logged - Yes or No? What is their logging behavior. Is this possible to Rollback in case of Truncate?

Now Run your queries in all these 3 scenarios to clear your doubts...

ANSWER:(All 3 scenarios when run independently i.e. not one after the other) gives same output i.e. In all 3 scenarios Transactions have been rolled back giving the following output:

 TestID
101
102
103
104
105

Scenario 4: What output will you get in this case?

BEGIN TRAN
--Truncate statement
Truncate Table TestTable
--DROP statement
DROP Table TestTable
-- Rollback Transaction
ROLLBACK TRAN
GO
-- Check output after Truncate and Drop
SELECT * FROM TestTable
GO

Answer : In this case also when both Truncate and Drop statements are there inside the BEGIN TRAN and ROLLBACK TRAN we get the same output i.e. Rollback has been done and output which we get is :

 TestID
101
102
103
104
105

Scenario 5: What output will you get in this case. When we specify drop before Truncate within BEGIN TRAN and ROLLBACK TRAN ?

-- Begin The Transaction
BEGIN TRAN
--DROP statement
DROP Table TestTable
--Truncate statement
Truncate Table TestTable
-- Rollback Transaction
ROLLBACK TRAN
GO
-- Check output after Truncate and Drop
SELECT * FROM TestTable
GO

Answer: In this case you will get This Message in the Messages Tab along with This output in Results Tab since the drop is used before Truncate within Transaction so finally whole Transaction is rolled back and the select statement outside the Transaction still comes up with 5 records.

Messages Tab:

Msg 4701, Level 16, State 1, Line 6
Cannot find the object "TestTable" because it does not exist or you do not have permissions.

Results Tab:

TestID
101
102
103
104
105

Having run all these queries Lets Draw out some conclusions and get our basics correct:

1. Within a Transaction TRUNCATE can be rolled back. As mentioned in Point No 4 below; Truncate also does logging but the nature of logging as compared to Delete is different. This is why in our examples above we were able to Roll back in case of Truncate.

2. Truncate can't be rolled back if no transaction is used. Truncate operation when you run outside a Transaction is auto committed so You will not be able to rollback if you use Truncate outside a Transaction. i.e. Once committed it is not possible to rollback Truncate and DROP.

3. TRUNCATE removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; However, TRUNCATE TABLE is faster because it uses fewer system & transaction log resources.

4. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. We can say A DELETE statement is always fully logged. TRUNCATE TABLE removes the data by de-allocating the data pages used to store the table data and records only the page de-allocations in the transaction log. Hence less Transaction Log space is used in case of TRUNCATE.

5. Truncate uses Fewer locks. When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. On the contrary TRUNCATE TABLE do not lock each row as Delete does but always locks the table (including a schema (SCH-M) lock) and page.

6. TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. We have to use DROP TABLE to remove the table definitions well as its data.

7.  TRUNCATE TABLE cannot activate a trigger because the operation does not log row - by - row deletions.

8. DROP removes one or more table definitions and all data, indexes, triggers, constraints and permission specifications for those tables. Any view or stored procedure that references d dropped table must be explicitly dropped by using DROP VIEW or DROP PROCEDURE statement.

9. If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter it is advisable to use DELETE.

10. You cannot use TRUNCATE TABLE on tables that are referenced by a FOREIGN KEY constraint. Instead, use DELETE statement without a WHERE clause.

11. You can truncate a table having a foreign key that references itself.

12. You cannot use TRUNCATE TABLE on tables that participate in an indexed view. Better use Delete in such cases.

13. DROP TABLE cannot be used to drop a table that's referenced by a FOREIGN KEY constraint. The referencing FOREIGN KEY constraint or the referencing table must first be dropped. If both the referencing table and the table that holds the primary key are being dropped in the same DROP TABLE statement, the referencing table must be listed first.

14. By default, a DELETE statement always acquires an exclusive (X) lock on the table it modifies, & holds that lock until the transaction completes. With an exclusive (X) lock, no other transactions can modify data; read operations can take place only with the use of the NOLOCK hint or read uncommitted isolation level. You can specify table hints to override this default behavior for the duration of the DELETE statement by specifying another locking method, however, it is not recommended and can lead to data inconsistency.

Reference : Self study, MSDN and other internet resources. 

Note: This blog is based on self study and is subject to be modified as and when I discover something new or find anything incorrect. Suggestions  and comments are welcome.

Regards,
Paras

What is a Precedence Constraint in SSIS Project Control Flow?

Precedence Constraint: In SSIS Project Control Flow, a Precedence Constraint defines the workflow between two executables. The Precedence Constraint can be based on a combination of the execution results and the Evaluation of Expressions.

A Precedence Constraint has the following Constraint Options:

A Package Designer has the option of choosing between the following 4 Evaluation Operations:

1. Constraint : Have 3 values Success(Green), Failure(Red), Completion(Blue).
2. Expression: Execution of the next task depends on the evaluation result(True or False) of the expression provided.
3. Expression and Constraint: Next task in the hierarchy runs based on options selected as Value of Constraint(Success, Failure, Completion) AND evaluation result of the expression.
4.  Expression or Constraint : Next task in the hierarchy runs based on options selected as Value of Constraint(Success, Failure, Completion) OR evaluation result of the expression.

Multiple Constraints: If the constrained Task has multiple constraints, you can choose how the constraints interoperate to control the execution of the constrained Task.

Options in Radio Button are:

1. Logical AND: All constraints must evaluate to TRUE.
2. Logical OR: One constraint must evaluate to TRUE

Some Important Properties of a Precedence Constraint are:

1. PrecedenceExecutable: The executable to wait on.
2. ConstrainedExecutable: The executable that will execute.
3. EvalOp: Any of the 4 Evaluation Operation explained above.
4. Expression: The expression to be evaluated.
5. Value: Succes, Failure or Completion
6. LogicalAnd: True or false
7. EvalutesTrue: Test if the precedence constraint will eavalute TRUE.

Note: The above post is based on self study and subject to be edited. Comments and Suggestions are very welcome.  Thanks...

Regards,
Paras