Results for category "Propagation"

SSIS Advanced Techniques Workshop

sqlsafety 0 Comments

I recently completed a set of videos for SSWUG that covers some advanced techniques for SQL Server Integration Services.

1. SSIS – Creating dynamic packages
2. SSIS – Error handling and troubleshooting
3. SSIS – Deployment models
4. SSIS – Replacing dynamic SQL with variables and expressions in SSIS
5. SSIS – Leveraging the Execute SQL task in SSIS

Each session covers advanced topics in SSIS and outlines each topic for SSIS 2005 though 2014. This workshop is available online at: SSWUG.

When registering for the workshop you can use the VIP Code “DAVID10” to receive $10 off.

SSWUG Summer Learning

SSWUG Summer Learning

SSIS Logging and Event Propagation

sqlsafety 1 Comment

While presenting at a recent conference on handling errors in SSIS I was demonstrating implementing logging and pointing out how event propagation affects the logged events. To make a long story short, every event for every task will propagate up to its parent, sometimes referred to as “bubbling up”.  In the case of logging errors this means that ever error that occurs will be logged a minimum of two times, once at the task and then once at the package.  I blogged about event propagation here.

To demonstrate this behavior consider a package with an execute SQL task that will create a table, pkerror, with a single column that maintains the primary key.

image


IF EXISTS(SELECT * FROM sys.tables WHERE name = ‘pkerror’)
BEGIN
DROP TABLE
pkerror
END

CREATE TABLE pkerror(
col1      INT PRIMARY KEY
);
GO

Within a data flow task intentionally cause a violation of the primary key in the pkerror table using a SELECT statement that will insert in duplicate key values:

image imageThen configure the package to log the OnError events using a SQL Server log provider:

image 

image

Using the SQL Server log provider creates a table called sysssislog in the database defined in the manager configured for the log provider.  Executing the package will record the error(s) that occur in a package and will then provide the ability to query the sysssislog table to review the recorded events.  Querying this table after executing our package you will find 6 OnError events that are logged for a primary key violation.

image

One thing that is important to keep in mind is that a single error can raise several error messages, but from reviewing the logged events we can see that each error message is raised once at the DuplicateKey data flow task and then again at the PropagationLogging package. 

imageSo the topic came up as to how to stop the logging of a single error multiple times.  Logging uses event handlers to fire the logged event that is then handled by the log provider which means that setting the tasks Propagate property to False will prevent the propagation of the task up to the package, or container.

In order to change the system variable Propagate we must first create an OnError event handler on the DuplicateKey task.

imageOnce an OnError event handler has been created then the Propagate variable can be changed from True to False.

imageOne question that I often get is why must an event handler be created when nothing is really done with it.  The reason for this is that the Propagate system variable is not available until an event handler has been created.

After configuring the propagation variable TRUNCATE the sysssislog table and re-execute the package.  Querying the sysssislog table now shows that the errors raised in the DuplicateKey data flow task did not propagate to the PropagationLogging package.

SSIS FailParentOnFailure

sqlsafety 1 Comment

The FailParentOnFailure task and container property sounds rather straight forward right?  If a task fails within a container and the FailParentOnFailure property is set to true the container should fail and if the property is set to false then the container should not fail.  Setting the FailParentOnFailure to true works as expected, but unfortunately when this property is set to false the container will still fail by default.  To demonstrate and explain this behavior consider a package with a sequence container that contains two execute SQL tasks connected with an on success precedence constraint and the first task is configured to succeed while the second is configured to fail.

image The first task is configured to execute SELECT GETDATE() while the other will raise a divide by zero error with the query SELECT 1/0.  The default setting for the FailParentOnFailure is false so executing this package would lead you to believe that the sequence container would show success regardless of the outcome of the failed task, but executing the package shows that this is not the case:

image image So what good is this property if the parent fails regardless of setting?  Despite the outcome the property is working as expected in this case.  The reason the container fails is because the OnError event propagates from the task to the sequence container.  In order for the FailParentOnFailure property to work as exepected you need to suppress propagation on the failure execute SQL task.  This can be done by configuring the failure tasks OnError event handler and changing the propagate system variable to false.  image

Once propagation is suppressed executing the package will show that the sequence container succeeds despite the failure task failing.

image

Another option is to change the MaximumErrorCount property of the sequence container from the default of 1 to whatever is an acceptable number of errors, but if the error count reaches your configured value the sequence container will again show failure.

image

I outlined precedence constraints and event propagation in earlier posts.

SSIS Event Propagation

sqlsafety 2 Comments

Event Propagation

In a recent post outlining how to report errors in SSIS I briefly discussed event propagation in an SSIS package. Creating the sample package for this post re-introduced me to some of the intricacies and nuances of event handlers. In my initial post I provided a link to a very insightfulvideo showing event propagation, sometimes referred to as “bubbling up”, by Jamie Thomson, but decided to follow up with more detail and examples.

The easiest way to begin to understand event propagation is by creating simple script tasks in a package that displays a message box saying “Script Task is executing”.

MessageBox.Show(“Script Task is executing”);

Within the script tasks PostExecute event handler tab add a script task that, again, displays a message box saying “PostExecute event handler from Script task”

MessageBox.Show(“PostExecute event handler from Script task”);

Finally add a script task to the PostExecute event handler for the package displaying a message box, but include the system variable System::SourceName as a ReadOnlyVariable. The message box text will say “Package PostExecute event handler called from “ and concatenate the task name that has “bubbled up

clip_image002[1]

MessageBox.Show(“Package PostExecute event handler called from ” + Dts.Variables[“SourceName”].Value.ToString());

Execution the existing package will result in displaying 4 different message boxes.

1. Message box showing the script task is executing

2. Message box showing the PostExecute event handler of the script task

3. Message box showing the package PostExecute event handler of the PostExecute script task

4. Message box showing the package PostExecute event handler of the initial script task

Again to give credit where it is due, Jamie Thomson’s video outlines this first example.

This sufficiently demonstrates how ALL package executables have associated event handlers, not just control flow tasks, and how each event handler will bubble up from the lower level tasks to any associated container(s) and finally the package. In this example the script task executed a script task on PostExecute and both script task and PostExecute script task bubbled up to the package PostExecute.

The above example is simple enough, but let’s add another degree of difficulty by adding a sequence task to the existing package and placing the script task in it. Finally add a script task to the sequence containers on PostExecute event handler that will display a message box saying “PostExecute event handler from Sequence container”.

MessageBox.Show(“PostExecute event handler from Sequence Container ”) + Dts.Variables[“SourceName”].Value.ToString()) ;

Despite the fact that only a single task container was added the results display how confusing event propagation can be. Executing the package will now result in the following message boxes to be displayed:

clip_image005

clip_image006

clip_image007

clip_image008

clip_image009

clip_image010

clip_image011

clip_image012

clip_image013

clip_image014

clip_image015

clip_image016

WOW!!! There are 12 total message boxes 11 of which were raised from the PostExecute event handlers. In order to try and outline the events in their proper order let me introduce a diagram of the package, its tasks, and the event handlers.

clip_image004

The diagram shows a single package containing a sequence container that has a PostExecute event handler and a script task with a PostExecute event handler. Upon executing the package each task will have a post execute event raised. The script task will bubble up to the sequence container task, which will in turn bubble up to the package. To further cloud propagation each script task in each event handler will also raise a PostExecute event which will also propagate.

Suppressing Propagation

There will obviously come a time where you will want to suppress events from bubbling up throughout a package and there are several methods available to do this. The first method is to set the System::Propagate variable within an event handler to false. Setting the propagate variable to false indicates that the event should not be propagated to a higher level. To provide a more direct example create a package with an execute SQL task that upon successful execution goes to a data flow task. The execute SQL task will create a table in the AdventureWorks2012 database called pkerror with a single column that has a primary key.

IF EXISTS(SELECT * FROM sys.tables WHERE name = ‘pkerror’)
BEGIN
DROP TABLE
pkerror
END
CREATE TABLE
pkerror(
col1 INT PRIMARY KEY
);
GO

The data flow task should have an oledb source that uses a union query to insert duplicate values, thereby causing a primary key violation error.

SELECT 1 AS pkey
UNION ALL
SELECT 1 AS pkey

In the data flow tasks OnError event handler include a script task that references the System::Propagate variable as ReadWrite and use the C# script below in the script body:

MessageBox.Show(“OnError event handler from DuplicateKey”);

Dts.Variables[“Propagate”].Value = false;

Finally place a script task in the OnError event handler of the package that displays a message box:

MessageBox.Show(“OnError event handler from Package”);

The control flow and data flow tabs should look similar to the diagram below:

Control Flow

clip_image018

Data Flow

clip_image019

Executing the package with the OnError data flow event handler set not to propagate will result in three message boxes showing that the data flow’s OnError even is triggered.

clip_image020

This might seem a bit confusing since a message box displays three separate times, but that is because three errors are raised. What is not raised is a message box showing that the event was raised from the package level. By commenting out the:

Dts.Variables[“Propagate”].Value = false;

in the PostExecute event handler script of the script task and re-running the package will display 6 message boxes, 3 from the OnError event handler of the data flow task and 3 from the OnError event handler of the package, which are propagated from the data flow task.

Another method that can be used to block event propagation is to set the higher level event handler “Disable” property to “True”:

clip_image001

If you experiment with the PostExecute event handler you will find that this is probably more desirable than setting the Propagate variable to false, and insures that the higher level event handler will not fire. The question often arises as how to dynamically define the “Disable” property of higher level event handlers. This can easily be done by creating a variable that will be used in the event handlers “Disable” property as an expression. The value of the variable can be set to True or False from a script task the same as the System::Propagate variable.

image

If you are interested in the sample packages that I created for this post please email me at sqlsafety@gmail.com and I will send them out.
If you are interested in how propagation affects the FailParentOnFailure property please refer to this post.


Hit Counter provided by Sign Holders