Developing and Debugging the Task

The preceding discussion applies to all the component types and shows how to start any one of the component type projects. From this point forward, this chapter covers how to write a task in detail. Before starting to build tasks, it's helpful to understand the general relationships between the various high-level elements in the SSIS runtime, so a review of Chapter 7, "Grouping Control Flow with Containers," is worthwhile, especially the discussion about Taskhosts. Also, because the CryptoTask is complete and implements all the task features, the remaining discussion focuses on the CryptoTask sample in the SAMPLES\SRC\SampleComponents\CryptoTask folder.

Giving the Task an Identity

As you've seen, without the DtsTask attribute, the SSIS runtime considers your task assembly as just another class library. The attribute is what identifies the class library as a task and provides information about the task that the designer can use to describe it. CryptoTask has the following DtsTask attribute in CryptoTask.cs.

[ DtsTask(   TaskType = "DTS90",   DisplayName="CryptoTask",   IconResource="SSIS2K5.Samples.Tasks.CryptoTask.TaskIcon.ico",   Description="A task for encrypting text files",   UITypeName = "SSIS2K5.Samples.Tasks.CryptoTask.CryptoTaskUI,      SSIS2K5.Samples.CryptoTaskUI, Version=1.0.0.1, Culture=Neutral,      PublicKeyToken=844aba2619516093",   TaskContact = "CryptoTask; Microsoft SQL Server Integration Services 2005,       By Kirk Haselden; For Version 9.0 of Integration Services;       (c) 2006 Kirk Haselden;http://www.CustomTask.com/CryptoTask") ] 

The TaskType parameter is not required, but is useful for identifying the target SSIS version for the task. The DisplayName parameter is required. The DisplayName is the name shown in the Toolbox and to name the task. If you don't provide a DisplayName parameter, the designer attempts to use the Description parameter value. If you don't provide either parameter, the designer cannot create the task. The IconResource parameter points to the icon to be used in the Toolbox. It is not required. If not provided, the designer uses a default icon. However, if you do provide this, the name of the resource must be "TaskIcon" and you must provide the fully qualified name. The UITypeName parameter is required if you want to provide a TaskUI for the task and contains the fully qualified type name of your Task UI class. The following is the format of the UITypeName attribute:

UITypeName = "NamespaceTypeName, AssemblyName, Version=AssemblyVersion, Culture=Neutral, PublicKeyToken= PublicKeyToken".

The UITypeName parameter seems to be the most problematic for custom task writers to get right, so this is covered in detail in the "Building the Task User Interface" section.

Provide the TaskContact parameter in case the SSIS runtime is unable to load the task. The SSIS runtime saves the TaskContact information with the task and displays it in the designer with an error when the task fails to load. This parameter is not required, but is suggested as a way to inform users how to correct the load problem. Chapter 7 describes this feature in detail.

Adding Feature-Specific Properties and Functions

You need to perform two types of coding when creating a task. One type is implementing the functionality that allows the task to function in the SSIS environment as a good SSIS citizen. The other type is the actual functionality that the task will provide to the packages that use it. For the CryptoTask, the task-specific behavior is provided by the encryption and decryption helper functions found in Rijndael.cs. All the task code is found in the CryptoTask.cs file.

Note

A quick search on the Web yields a robust and highly reusable code library for encryption that Obviex wrote and freely distributes. This library is entirely contained with the Rijndael.cs file in the CryptoTask sample solution. To find out more about the folks at Obviex, go to their website at http://www.obviex.com.

Thanks to Alex Bandukov and the folks at Obviex for permission to use this code.

Adding the Feature Properties

Although these properties are specific to the CryptoTask, they are also fairly representative of the kinds of properties most tasks will need, and so this is a good set to discuss. The CryptoTask has five feature-related properties, as follows:

• Password

• SourceFileConnectionManager

• DestinationFileConnectionManager

• EncryptionOperation

• OverwriteDestination

Passwords and Other Sensitive Properties

Passwords are interesting properties because of their sensitive nature. A number of conventions are used to avoid displaying the password.

Write Only

Passwords should always be write-only to prevent accidental exposure of the password. The code for the Password property is simple:

public string Password {    set    {       m_password = value;    } } 

Note that there is only a SET method and no GET method. The code gets interesting when it is saved.

Caution

It is very important that password encryption and other protection measures discussed in this section be considered only as password obfuscation and do not protect passwords 100% from those with unfettered package access. In other words, if you have a package that is encrypted, and you give the package and password for the package to another person, having a write-only password property on a task or marking the password as sensitive only makes it difficult to get the password, not impossible. To fully protect passwords in packages, you must use one of the encryption package protection options and not share the password. As soon as someone has access to the unencrypted package, all data within the package should be considered compromised.

Marked as Sensitive

To allow the SSIS runtime to detect properties that should be encrypted, properties must be marked as sensitive in the SaveToXML method.

SaveToXML

// Save the password private const string PERSIST_CONST_SENSITIVE    = "Sensitive"; private const string PERSIST_CONST_PASSWORD     = "Password"; // Create the password element XmlElement pswdElement = doc.CreateElement("", PERSIST_CONST_PASSWORD, ""); rootElement.AppendChild(pswdElement); // Set the password element as sensitive so that the runtime will encrypt it. pswdElement.InnerText = m_password; XmlAttribute attr = doc.CreateAttribute(PERSIST_CONST_SENSITIVE); attr.Value = "1"; pswdElement.Attributes.Append(attr) 

This code creates a new element for the password value and adds an attribute called Sensitive, setting the value equal to 1 or trUE. The SSIS runtime, when saving the task, looks for all properties with the Sensitive attribute and encrypts the value for the property in memory before saving to the destination. In this way, the value is never written out to disk.

Masked in the UI

Passwords should always be masked in the UI. This is covered later in the "Building the Task User Interface" section.

Connection Managers

The SourceFileConnectionManager and DestinationFileConnectionManager properties are of type string and store the names of the connection managers the CryptoTask uses. Here again, the properties are simple accessor methods, but with a twist.

// SourceConnection manager public string SourceFileConnectionManager {    get    {       return GetConnectionName(m_connections, m_source);    }    set    {       m_source = GetConnectionID(m_connections, value);    } } 

Using names to reference connection managers makes sense from a human perspective. Humans don't remember numbers, especially globally unique identifiers (GUIDs) as well as they do names. However, connection manager names change. Their IDs, which are GUIDs, don't. So, the connection manager properties actually store the GUID ID of the connection manager instead of the name. That way, if the user changes the connection manager name, the connection manager reference is still valid. The GetConnectionName and GetConnectionID functions are conveniently provided on the base task class. All you need to do is store a reference to the connections and pass it in. The methods do the work of getting the name from the ID and the ID from the name. The task should only store the ID because it is guaranteed not to change, unless you edit the package XML directly, of course.

Note

Hard-core .NET developers will perhaps cringe at the member variable names using the "m_" prefix. FxCop might emit a warning about this not being Pascal cased. Ignore it. Resist the flood of conventionalism!

EncryptionOperation

The EncryptionOperation property illustrates how custom types can be used in tasks to clearly describe optional task behavior. EncryptionOp is an enum defined within the task and represents the two operations the task can perform. A Boolean property could have worked here, but the values Encrypt and Decrypt are much more direct and clearly indicate what the operation will be. Later, if you decide to modify the task to perform more operations, adding another enum value is easy.

public EncryptOp EncryptionOperation {    get    {       return m_operation;    }    set    {       m_operation = value;    } } 

Again, the EncryptionOperation is a simple accessor property. This is how properties on tasks should be. There should rarely be any complicated logic in task properties. Also, the functionality of the task is generally something that can and should be developed and tested separately. For example, in the CryptoTask example, the code that interacts with the SSIS runtime is contained in the CryptoTask.cs file and the code that implements the actual features, the functionality of the task, is contained in the Rijndael.cs file. This makes it easy to isolate, test, and debug the code.

Adding SSIS-Specific Properties and Functions

As you have seen, you build customassembly. The base class implements three methods that you should override when creating a custom task:

• InitializeTask Do initialization functions, such as creating log events, custom events, and breakpoints

• Validate Validate if the task will run successfully when Execute is called

• Execute Do the work the task is configured to do

There are also some execution-related properties that the custom task can provide:

• ExecutionValue Provides a task-defined value with information resulting from the task execution.

• WaitForMe Indicates if the task is an event-driven task. If the task has no bounded execution lifetime, this property should be set to FALSE. The default is TRUE.

In addition, there are some properties and a method related to task upgrade:

• CanUpdate Indicates if the task supports upgrading other tasks

• Version Indicates the version of the task

• Update Tells the task to update another task

Finally, there are two utility methods for handling connection manager names and IDs just mentioned:

• GetConnectionName

• GetConnectionID

Only the Execute method must be implemented. The Validate method is extremely important as well. However, the rest of the methods need to be implemented only if you want your task to have the functionality that they provide.

The InitializeTask Method

The InitializeTask method is called when the task is first created. In the designer, that happens when you drag the task from the Toolbox and drop it onto the Control Flow surface or when loading a package. Following is the method signature:

public virtual void InitializeTask(   Connections connections,              //The package connections reference   VariableDispenser variableDispenser,  //The package variables reference   IDTSInfoEvents events,                //The collection you use to raise events   IDTSLogging log,                      //The package logging reference   EventInfos eventInfos,                //The collection used to describe events   LogEntryInfos logEntryInfos,          //The collection used to describe log entries   ObjectReferenceTracker refTracker     //This parameter should be ignored. ) 

You can perform any initialization that is needed here; however, this method is provided as a way for the task to communicate to the SSIS runtime what events the task will raise and what log entries it will log. You might notice that the events parameter is different than the one passed into Validate and Execute. This is because the runtime is only prepared to receive Error, Warning, and Information events at the time it calls InitializeTask.

Creating Custom Events

Tasks use the EventInfos parameter to describe custom events they raise. The SSIS runtime exposes the events to the designer where users can create event handlers for the custom events. The code to add a custom event with two parameters is as follows:

string[] parameterNames = new string[2] {"Parameter1", "Parameter2"}; TypeCode[] parameterTypes = new TypeCode[2] {TypeCode.String, TypeCode.Int32}; string[] parameterDescriptions = new string[2] {"Parameter desc1.", Parameter desc2" }; Boolean allowEventHandlers = true; eventInfos.Add(    "CustomEventName",    "A custom event to show how they are created",    allowEventHandlers,    parameterNames,    parameterTypes,    parameterDescriptions ); 

The first parameter is a string containing the name of the event. The second parameter is a string containing the description. The third parameter is a Boolean and specifies if event handlers should be created for the event. The last three parameters are for describing the event parameters. The SSIS runtime uses these parameters to build system variables on the event handler that will contain the event parameters.

Adding a custom event has the following effects:

• A new Log event is added for the event. If the Log event is enabled in the Configure SSIS Logs dialog box, whenever the task fires the event, the SSIS runtime also logs the event.

• A new event handler type becomes available in the EventHandlers collection. Figure 24.7 shows the event handler for the custom event added with the sample code.

• A new system variable becomes available within the event handler for each event parameter. Figure 24.8 shows the two system variables, Parameter1 and Parameter2, that will contain the values for the custom event parameters created in the sample code.

Figure 24.7. The sample custom event handler in the designer

Figure 24.8. The system variables that will contain the values for the custom event parameters

Creating Custom Log Events

The task must add a LogEntryInfo for custom log events. This informs the SSIS runtime about the log entry so that it can filter the log entry and provides an entry in the designer's Configure SSIS Logs dialog box. Figure 24.9 shows the custom log entry for the CryptoTask called CryptoFileLogEntry. Also, notice the custom log entry for the custom event called CustomEventName created with the sample code.

Figure 24.9. The CryptoTask custom log entry and custom event log entry

The code to add this custom log entry is as follows:

// Adds a log entry info so users can filter the log entries of this type in the designer. // Creates an entry in the logging dialog box under details to filter the log entry. logEntryInfos.Add(    "CryptoFileLogEntry",    "Logs the file and encryption operation performed",    DTSLogEntryFrequency.Occasional ); 

The first parameter is a string that contains the name of the log entry. The second parameter is a string that contains the description of the log entry. The third parameter is the LogEntryFrequency parameter and indicates how often the task logs the custom log entry. The SSIS runtime currently ignores this parameter.

The Validate Method

Validate is called whenever the runtime wants to ensure that the task is valid. It is guaranteed to be called at least once immediately before the Execute method is called. It can be called multiple times before and after execution.

public virtual DTSExecResult Validate(    Connections connections,             //Used to access connection managers    VariableDispenser variableDispenser, //Used to access variables    IDTSComponentEvents componentEvents, //Used to raise events    IDTSLogging log                      //Used to make log entries ) 

Validation is what a component does to detect if there are any issues that would cause it to fail during execution. The following are some guidelines for what your component should do during validation:

• When a component validates, it should always validate that given its current property settings, it will succeed during execution.

• Components should not return when they find the first error or warning. They should continue to validate until finding all errors and warnings. This provides more information and helps the user better understand all the problems with the component up front because an entire error stack for the component is visible instead of just one error or warning.

• Components should emit warnings for less severe errors. But that can be a difficult call to make. Where do you draw the line between warnings and errors?

To Warn or To Err

You should think of warnings as errors that won't cause the component to fail. For example, the Send Mail Task emits a warning when there is no subject specified, but it emits an error when there is no connection manager specified. Clearly, the former isn't critical, whereas the latter causes the task to fail.

Another example is the validation code for the CryptoTask. If no password is specified, this is bad. The document gets encrypted, but without a password. Anyone can decrypt the document by simply running the task without a password. So, it is important to ensure that there is a password and emit an error if there isn't one.

// Validate that the password exists if (m_password.Length == 0) {     events.FireError(15, "",     "There is no password specified.", "", 0); } 

You can also validate that the password conforms to a password complexity policy. For the sake of simplicity, the code checks if the password is empty. You definitely want to emit an error in this case because the task will be silently behaving in an unexpected way.

On the other hand, there are cases when the task is expecting that something will be configured in a certain way or that a given file exists but doesn't. Packages are sometimes built to prepare files for processing by other parts of the same package. For example, to error if a file doesn't exist would essentially eliminate this pattern and create an inflexible component. When it comes to elements of the system that can be altered by previous package control flow or data flow, it is wise to only emit a warning. The ValidateConnectionManager function in CryptoTask.cs emits a warning for this case, as follows:

// Does the file even exist? // If not and it's the source Connection Manager, let the user know. if (!fiSource.Exists) {    if( !bDest) // Don't warn if it's the destination cuz we'll be creating the dest file.    {       // Only fire a warning because the file may become available later in the package.       StringBuilder sb = new StringBuilder("The specified connection manager : " +           sCMName + " points to the following file which does not exist : " + sFile);       events.FireWarning(3, "", sb.ToString(), "", 0);    } } 

If the source file to encrypt doesn't exist, the CryptoTask emits the following warning:

Warning   1 Validation warning. Encrypt File : The specified connection manager : SourceFile points to the following file which does not exist : D:\SAMPLES\SAMPLE.TXT 

Seriously consider what constitutes a validation warning or error. Although the runtime design encourages static package designs, a certain amount of flexibility is necessary. Think about how the task will be used in different situations. Issues that cannot be resolved at execution time should always be considered errors. The following are some examples of issues that should always be considered errors:

• Missing connection manager Connection managers can only be created at design time. The package cannot correct the problem during execution time.

• Silent unexpected behavior Task would behave in a way that is undesirable because, for example, a property value is out of bounds.

• Missing variables Opinions differ, but it is safest to emit an error.

• Internal errors Always catch exceptions and correct the problem internally or emit an error with a detailed explanation. This is the one case in which validation should return Failure.

Errors Are Not Failures

There has been some confusion about how errors affect the success or failure of validation or execution. Errors do not constitute failure. It is the MaximumErrorCount property on containers that defines how many errors constitute failure. The Validate and Execute methods should never return Failure because of errors or warnings except when the errors are a result of an exception that cannot be corrected in code. Typical cases are when there is an exception or fatal error such as when the AcquireConnection call fails to return a valid connection.

The Execute Method

The Execute method is where the task performs its work. Execute cannot be called at all if the package flow doesn't reach the task or if the task is disabled. Otherwise, it is called at least once during the execution of a package and can be called more than once if the task is in a loop. The parameters are the same as for validation with the addition of the transaction object.

public virtual DTSExecResult Execute(    Connections connections,    VariableDispenser variableDispenser,    IDTSComponentEvents componentEvents,    IDTSLogging log,    object transaction ) 

The Connections Parameter

At design time, a user creates a new connection manager and sets its properties. During execution, tasks retrieve the desired connection manager in the collection and call AcquireConnection on the connection manager, which yields a number of values depending on the connection manager type. For example, the Flat File Connection Manager returns a string containing the fully qualified filename of the flat file, the OLE DB Connection Manager returns a pointer to an IDBSession OLEDB interface, and an ADO.NET Connection Manager returns an IdbConnection object.

How Tasks Use Connections

Tasks use connections by creating a task property where the name or ID of the connection can be specified. At design time, the user places the name or ID of the connection to use by setting this property. In the CryptoTask, these properties are called DestinationFileConnectionManager and SourceFileConnectionManager. During execution, the task uses the name or ID to retrieve the specified connection manager and call AcquireConnection. Here is the code from the CryptoTask.

// Get the source filename string ConnectionManager cm = connections[m_source]; if (cm != null) {    // Get the filename    sourceFileName = (string)cm.AcquireConnection(null); } 

The m_source member variable is a string that contains the ID of the source connection manager. The AcquireConnection call takes one parameter, which is a transaction object. In this case, the File Connection Manager does not support transactions, so simply passes in null. Later, the code uses the returned string, sourceFileName, to open the file to encrypt it with this code.

// Read the file into a string TextReader trSource = System.IO.File.OpenText(sourceFileName); string sSource = trSource.ReadToEnd(); // Release the file trSource.Close(); 

The VariableDispenser Parameter

The second parameter is the VariableDispenser object. Users create variables and populate them by directly entering values, configuring them with package configurations, or by adding an expression to the variable. Variable values can also change during the execution of the package. The VariableDispenser provides accessor methods for retrieving variables.

How Tasks Use the Variable Dispenser

Generally, a task exposes a property with a name that indicates it is used to store a variable name or ID. A name like ConnectionStringVariable is appropriate. Similar to the way connections are used, the task uses the name as an index into the Variables collection to retrieve the specified variable during execution. Because SSIS packages do work in parallel, variables can have multiple simultaneous readers and writers. Therefore, it is necessary to serialize access to variables. The runtime does this using what is called the Variable Dispenser. To gain access to a variable, the task must first reserve the variable. This essentially locks variables so that other tasks or components cannot access them simultaneously. The following code is an example of how to access variables:

// Lock the variable with the initialization vector string variableDispenser.LockForRead(m_InitVectorVariable); // Get the locked variables collection // In this case the collection has only the one variable, InitVector. variableDispenser.GetVariables(ref variables); // Use the variable value string sVector = variables[m_InitVectorVariable].Value; 

The IDTSComponentEvents Parameter

The third parameter passed is the Component Events interface. The ComponentEvents interface provides a way for the task to raise events. Events that a task raises can be handled within the package in event handler workflow or simply flow up through the container hierarchy until it reaches the package. After the event arrives at the package, it is sent to the client events interface passed into the Execute method on the package. Figure 24.10 illustrates how a typical event is generated and propagated through the package. The solid lines represent method calls and the dotted lines represent the method returning. The client represents the designer, DTExec.exe, or any other object model client. The client passes in an IDTSEvents interface so that it can "listen" for all events that are raised within the package as it executes. Likewise, each container also implements the IDTSEvents interface. Events bubble up through the container hierarchy, giving each container an opportunity to handle the event before passing it on. Figure 24.10 illustrates the life cycle of an event starting with the client application calling the package's Execute method and finishing with the event call returning to the task and the task continuing execution. Events are synchronous, meaning that when the task raises an event by calling one of the "fire" methods on the IDTSComponentEvents interface, the method does not return until the event has completed the entire event handling cycle from the event handlers all the way up to the client application.

Figure 24.10. The life cycle of task-generated events

Notice that each container passes its own IDTSEvents interface into the Execute method of its child executable and that the Taskhost has the OnError event handler defined in its EventHandlers collection. Notice also, that the OnError method is called on each container in the hierarchy, providing an opportunity for each container to handle the event. This design makes it possible to localize event handlers to the section of the package that raises the event. It also makes it possible to generalize event handling, that is, create global event handlers that handle all events from the entire package. If an event handler was defined for OnError at package scope, that event handler would be called for each OnError event in the package. The event bubbles up to the client where events can be reflected in the UI or trigger some logic in the client. For example, the designer uses the ExecutionStatusChanged event to determine whether to color the tasks red or green when they complete execution.

The following events are available on the Component Events interface:

• FireBreakpointHit This event is fired when an active breakpoint is hit. For more about implementing breakpoints, see the "Developing and Debugging the Task" section later in this chapter.

• FireCustomEvent This event is for custom, task-specific information. See "Firing Custom Events" in the next section.

• FireError This event is for reporting critical errors.

• FireInformation There are many situations when the task needs to provide information about internal events that are not appropriately surfaced as warnings or errors. For example, some tasks can feature the ability to execute in a smart way. Maybe they optimize some aspects of their configuration or change the way they execute based on some heuristic. The custom task should alert the user to this behavior by raising a FireInfo event. For another example, consider a custom task that uses the wildcard feature on the file connection manager to process multiple files that are unknown at package design time. By raising a FireInfo event for each file used, there is a record of which files were processed and in what order. You should raise the FireInfo event whenever there is nonerror information available that will help the user understand what is happening inside the task.

• FireProgress This event is for reporting progress. Tasks should raise this event to inform clients about the status of the task's execution. Even for short-running tasks, when the progress goes from 0% to 100% instantly, this event should be called.

• FireQueryCancel At various intervals in the execution of the task, the task should call this event. This is especially true if the task has the potential to be long running. Long-running tasks are those that take much more time than the other tasks in the package or longer than 15 or 20 seconds. This allows the runtime to gracefully terminate execution. The task raises this event to determine whether the package wants the task to cease executing. Because the package gives control to the task to execute and will not terminate it randomly, there needs to be a juncture at which the package can inform the task that it is attempting to shut down. OnQueryCancel provides that juncture.

• FireWarning This event is for reporting noncritical errors.

Firing Custom Events

The discussion so far has focused on how to raise events with the "Fire" events methods. Custom events are the extensible mechanism for raising task-defined events. Tasks can advertise the custom event by populating a special collection of event information objects that the runtime reads. This makes it possible to create DTS event handlers in the package workflow that will handle the events. Although the available set of stock events is rich and useful, there are many circumstances when the stock events don't provide the needed functionality. For example, a task might need to raise a custom event when a hard drive is full. The task could raise a custom DTS event with information about which drive is full and workflow can be created in the DTS event handler to clean up the hard drive.

Custom events are added during design time. As the package writer modifies properties and operations, the task is free to add and remove custom events. If you've defined a custom event, you'll need to use the FireCustomEvent method, as follows:

// Check whether the client wants to continue receiving events. Boolean fireAgain = true; // Set up the event parameters object[] objArgs = new object[2] {"Parameter1 Value", 42}; // Fire the custom event events.FireCustomEvent(    "CustomEventName",    "Custom event fired",    ref objArgs, "", ref fireAgain ); 

The SSIS runtime places the passed parameter values into the system variables it created to hold the values. Notice that the parameter values passed in are "Parameter1 Value" for the string parameter and "42" for the integer parameter. Figure 24.11 shows the custom event parameter system variables in the Watch window.

Figure 24.11. Viewing the value of custom event parameter system variables

The IDTSLogging Parameter

The IDTSLogging parameter is a pointer to the logging interface the task can use to create log entries. IDTSLogging has one property and two methods.

• Enabled Specifies if logging is enabled for the task

• GetFilterStatus Specifies if an event is filtered

• Log Adds log entries when called

In practice, the GetFilterStatus method is rarely used. If you do use it, call it once in the Execute method to determine if a given log entry has been filtered. If so, you can eliminate the log.

The Enabled property is useful to determine if logging is enabled for the task. If not, you can short circuit the log calls and save some time.

The Log method has the following signature:

void Log (    string eventName,    string computerName,    string operatorName,    string sourceName,    string sourceGuid,    string executionGuid,    string messageText,    DateTime startTime,    DateTime endTime,    int dataCode,    ref byte[] dataBytes ) 

The SSIS runtime overwrites the computerName, operatorName, sourceName, sourceGuid, and executionGuid. Even if you provide values for these parameters, they will not be logged. For most tasks, it is easier to have a helper function for logging, such as the following:

// A simplified method for logging. // Does not require the additional overwritten parameters. private void WriteLog(IDTSLogging log, string logEventName, string message) {    // Only log if we have a valid log object and logging is enabled    if (log != null && log.Enabled)    {       byte[] nothing = null;       log.Log(          logEventName, null, null, null, null, null,          message, DateTime.Now, DateTime.Now, 0, ref nothing );    } } 

The TRansaction Object

The last parameter passed into the Execute method is the transaction. Transactions in SSIS are thin wrappers around an ITransaction Distributed Transaction pointer. The transaction object has two properties, one is the ITransaction pointer and the other is a Boolean flag called isActive. The isActive flag is used internally to evaluate the transaction requirements imposed on the containers and is not of particular interest to the task author nor should it be modified in the task code.

Package writers configure transactions on the Taskhost or other parent container in the tasks hierarchy and at execution time, the container creates the distributed transaction and passes the transaction pointer to its children when it calls their Execute method. If your task uses a connection to a server that supports transactions and you want your task to participate in transactions, using the transaction object is simple. When the SSIS runtime calls the task's Execute method, it passes in the transaction object. The responsibility of the task is to pass the TRansaction object into the connection when calling AcquireConnection. If your task uses a connection that doesn't support transactions, the transaction object is of no interest to your task and the task should pass null to the AcquireConnection call.

The scope and lifetime of the transaction is determined by the owning container, which is the container that creates the transaction. The transaction is active as long as its owning container is executing. Depending on how the transaction is configured and the execution result of the owning container, the owning container commits or rolls back the transaction when the container completes execution.

public override DTSExecResult Execute( Connections connections,                VariableDispenser variableDispenser,                IDTSComponentEvents events,                IDTSLogging log,                object transaction) {     // Get the connection manager.     AcquireConnection(connections, transaction);     //Other execution code that uses the connection...     return DTSExecResult.Success; } 

Task Breakpoints

To expose breakpoints, a task must implement the IDTSBreakpointSite interface which is derived from IDTSSuspend. When a task is first created, the runtime calls IDTSBreakpointSite.AcceptBreakpointManager on the task, passing in a breakpoint manager that the task can use to inform the runtime of the breakpoints it wants to expose and to check whether those breakpoints have been enabled by the client. The task retains a reference to the breakpoint manager and creates breakpoints during the AcceptBreakpointManager call. The task then calls IsBreakpointEnabled while executing. Each breakpoint is identified by a task-defined and task-scoped ID. The following code shows how to create a custom breakpoint:

AcceptBreakpointManager( BreakpointManager breakPointManager) {    m_pManager = breakPointManager;    m_pManager.CreateBreakpoint(1, "Break when the task is about to do X"); } 

Then, in the Execute method, the following code is called when the breakpoint is hit:

Execute(...) {    // Other code...   // At the point in the code where you wish to fire the breakpoint   if( m_pManager.IsBreakpointTargetEnabled( 1 ) == true )   {      events.OnBreakpointHit( m_pManager.GetBreakpointTarget( 1 ) );   }   // Other code... } 

The IsBreakpointEnabled function must be called each time the task encounters a breakpoint in its code. This can become expensive if called too much. As a result, IDTSBreakpointSite contains a flag, DebugMode, which is set to trUE when the executable is to be debugged. When this flag is set to FALSE, the task can avoid the breakpoint logic altogether and save some performance. Figure 24.12 illustrates the order of processing that occurs when execution reaches a task breakpoint.

Figure 24.12. The order of processing for breakpoints

ExecutionValue

ExecutionValue is a read-only variant property. This property is a task-defined value that provides an opportunity for the task writer to expose more information about the execution of the task than merely if the execution succeeded or failed. For example, the Execute Process Task returns the exit code from the last process executed in this property. As a task author, you are free to use this property for whatever purposes you deem appropriate. However, the property should hold a value that relates to the execution of the task and should add information about the reasons for the success or failure of the task.

WaitForMe

The WaitForMe property is a special read-only Boolean property the task can expose to indicate that the package should not wait on the task to complete and set the Cancel event. Remember that the Cancel event is the windows event that the SSIS runtime flags to indicate that it is attempting to shut down, and tasks access the event through the CancelEvent system variable. The WaitForMe property is implemented in the task base class and is TRUE by default. This property should only be implemented in custom tasks that implement eventing. Eventing class tasks are tasks that have no defined execution life cycle. For example, the stock WMI Event Watcher Task is of this class. This type of tasks waits for events and when an event happens, they can return from their Execute method or continue waiting for more events for an indeterminant length of time.

The SSIS runtime knows it is time to return from executing when every task in the package has completed. However, if there is an eventing task still waiting, the package would never complete. So packages wait until all tasks with the WaitForMe property set to TRUE have completed. Then they signal the Cancel event, tHRough the CancelEvent system variable. Eventing class tasks should wait on this event and when it is signaled, cancel execution, and return, allowing the package to complete.

Update Properties and Method

Integration Services provides a way for later version tasks to update earlier version tasks. For example, if you ship a task and then, a year later, update the task, you'd want the newer task to update the older task. This is what the Update methods are for. CanUpdate has the following signature:

public virtual bool CanUpdate(string CreationName) {    return false; //default implementation } 

The string parameter contains the CLSID or fully qualified name of a task. Return trUE from CanUpdate if the task can update the task with the given CreationName.

Version is the version of the task. The default is 0 and should be incremented whenever you update a task.

public virtual int Version {    get{return 0;}    //default implementation } 

The Update method is called by the SSIS runtime to perform the update of the older task. The persisted state of the older version of the task is passed, giving the newer version task a chance to read in the property values for the older task and replace the XML with its own persisted state. In this way, the property values for the older task are retained while simultaneously upgrading to a newer version.

public virtual void Update(ref string ObjectXml) { } 

Saving and Loading Tasks

DTS packages are persisted in XML. Tasks can participate in persistence by deriving the task from IDTSTaskPersist, which has two methods called SaveToXML and LoadFromXML that must be implemented. The following code illustrates in a basic way how this can be done. Error handling and other code is omitted in the interest of clarity. For more sample code, see the CryptoTask sample task.

using System; using System.Xml; using Microsoft.SqlServer.Dts.Runtime; class MyTask : Task, IDTSTaskPersist {    // Other code...    // IDTSTaskPersist interface methods    public void SaveToXML(XmlDocument doc)    {         // Create element for the task info w/ prefix, name, namespace         XmlNode dataNode = doc.CreateElement( null, "MyData", mytask.uri");        // Append the node to the package document        doc.AppendChild(dataNode);        // Add text to the element, the value of MyTask's member data        XmlNode textNode = doc.CreateTextNode( m_value.ToString() );        // Add the node under our main node.        dataNode.AppendChild(textNode);    }    public void LoadFromXML(XmlElement element)    {    m_value = System.Convert.ToInt32(element.FirstChild.Value);    }    private int m_value = 123; } 

There are a few things worth mentioning about the previous snippet of code. First, notice that SaveToXML takes one parameter that is an XmlDocument while LoadFromXML takes an XmlElement as the parameter. When loading, the runtime creates an instance of the task and passes in the task's node. Everything below the task node, including content and format, is opaque to the package. So tasks have total control over what is stored inside their node. The one exception is that nodes marked as sensitive are handled in a special way as previously mentioned in the discussion "Passwords and Other Sensitive Properties." Also, if a task is simple with no special persistence requirements, you can simply not implement IDTSTaskPersist and the SSIS runtime will save and load your task for you.