Searching packages or just enumerating through all tasks is not quite as straightforward as it may first appear, mainly because of the way you can nest tasks within other containers. You can see this illustrated in the sample package below where I have used several sequence containers and loops. To complicate this further all containers types, including packages and tasks, can have event handlers which can then support the full range of nested containers again. Towards the lower right, the task called SQL In FEL also has an event handler not shown, within which is another Execute SQL Task, so that makes a total of 6 Execute SQL Tasks 6 tasks spread across the package. In my previous post about such as adding a property expressionI kept it simple and just looked at tasks at the package level, but what if you wanted to find any or all tasks in a package? For this post I've written a console program that will search a package looking at all tasks no matter how deeply nested, and check to see if the name starts with "SQL". When it finds a matching task it writes out the hierarchy by name for that task, starting with the package and working down to the task itself. The output for our sample package is shown below, note it has found all 6 tasks, including the one on the OnPreExecute event of the SQL In FEL task TaskSearch v1.0.0.0 (1.0.0.0)
Copyright (C) 2009 Konesans Ltd
Processing File - C:\Projects\Alpha\Packages\MyPackage.dtsx
MyPackage\FOR Counter Loop\SQL In Counter Loop
MyPackage\SEQ For Each Loop Wrapper\FEL Simple Loop\SQL In FEL
MyPackage\SEQ For Each Loop Wrapper\FEL Simple Loop\SQL In FEL\OnPreExecute\SQL On Pre Execute for FEL SQL Task
MyPackage\SEQ Top Level\SEQ Nested Lvl 1\SEQ Nested Lvl 2\SQL In Nested Lvl 2
MyPackage\SEQ Top Level\SEQ Nested Lvl 1\SQL In Nested Lvl 1 #1
MyPackage\SEQ Top Level\SEQ Nested Lvl 1\SQL In Nested Lvl 1 #2
6 matching tasks found in package.
The full project and code is available for download below, but first we can walk through the project to highlight the most important sections of code. This code has been abbreviated for this description, but is complete in the download.
First of all we load the package, and then start by looking at the Executables for the package.
// Load the package file
Application application = new Application();
using (Package package = application.LoadPackage(filename, null))
{
int matchCount = 0;
// Look in the package's executables
ProcessExecutables(package.Executables, ref matchCount);
... // // ...
// Write out final count
Console.WriteLine("{0} matching tasks found in package.", matchCount);
}
The ProcessExecutables method is a key method, as an executable could be described as the the highest level of a working functionality or container. There are several of types of executables, such as tasks, or sequence containers and loops. To know what to do next we need to work out what type of executable we are dealing with as the abbreviated version of method shows below.
private static void ProcessExecutables(Executables executables, ref int matchCount)
{
foreach (Executable executable in executables)
{
TaskHost taskHost = executable as TaskHost;
if (taskHost != null)
{
ProcessTaskHost(taskHost, ref matchCount);
ProcessEventHandlers(taskHost.EventHandlers, ref matchCount);
continue;
}
... // // ...
ForEachLoop forEachLoop = executable as ForEachLoop;
if (forEachLoop != null)
{
ProcessExecutables(forEachLoop.Executables, ref matchCount);
ProcessEventHandlers(forEachLoop.EventHandlers, ref matchCount);
continue;
}
}
}
As you can see if the executable we find is a task we then call out to our ProcessTaskHost method. As with all of our executables a task can have event handlers which themselves contain more executables such as task and loops, so we also make a call out our ProcessEventHandlers method. The other types of executables such as loops can also have event handlers as well as executables.
As shown with the example for the ForEachLoop we call the same ProcessExecutables and ProcessEventHandlers methods again to drill down into the hierarchy of objects that the package may contain. This code needs to explicitly check for each type of executable (TaskHost, Sequence, ForLoop and ForEachLoop) because whilst they all have an Executables property this is not from a common base class or interface.
This example was just a simple find a task by its name, so ProcessTaskHost really just does that. We also get the hierarchy of objects so we can write out for information, obviously you can adapt this method to do something more interesting such as adding a property expression.
private static void ProcessTaskHost(TaskHost taskHost, ref int matchCount)
{
if (taskHost == null)
{
return;
}
// Check if the task matches our match name
if (taskHost.Name.StartsWith(TaskNameFilter, StringComparison.OrdinalIgnoreCase))
{
// Build up the full object hierarchy of the task
// so we can write it out for information
StringBuilder path = new StringBuilder();
DtsContainer container = taskHost;
while (container != null)
{
path.Insert(0, container.Name);
container = container.Parent;
if (container != null)
{
path.Insert(0, "\\");
}
}
// Write the task path
// e.g. Package\Container\Event\Task
Console.WriteLine(path);
Console.WriteLine();
// Increment match counter for info
matchCount++;
}
}
Just for completeness, the other processing method we covered above is for event handlers, but really that just calls back to the executables. This same method is called in our main package method, but it was omitted for brevity here.
private static void ProcessEventHandlers(DtsEventHandlers eventHandlers, ref int matchCount)
{
foreach (DtsEventHandler eventHandler in eventHandlers)
{
ProcessExecutables(eventHandler.Executables, ref matchCount);
}
}
As hopefully the code demonstrates, executables (Microsoft.SqlServer.Dts.Runtime.Executable) are the workers, but within them you can nest more executables (except for task tasks).Executables themselves can have event handlers which can in turn hold more executables. I have tried to illustrate this highlight the relationships in the following diagram.
Download
Sample code project TaskSearch.zip (11KB)
