SSIS API: Introduction to Control Flow objects

Posted on by

Resources:

Control Flow objects for the most part hew closely to the SSIS Designer, but we’ll see some classes here that have no GUI analog, and will have to do some recasting to get specific properties.

The Executable abstract class corresponds to what we see listed in the Control Flow toolbox. Like the ConnectionManager, there’s no strong typing here, and creation is controlled by name strings:

//using Microsoft.SqlServer.Dts.Runtime;
//Package pkg; - a loaded Package
//string cfType = "STOCK:BulkInsertTask"; - the name string of the control flow desired
Executable ex = pkg.Executables.Add(cfType);

Containers:
We’ve got a couple different Control Flow types – containers, and the tasks themselves. There’s only a few containers, but unfortunately there’s no generic Container type, so we have to check for each separately:

if (ex is Sequence)
{
    Sequence seq = ex as Sequence;
}
else if (ex is ForLoop)
{
    ForLoop forloop = ex as ForLoop;
}
else if (ex is ForEachLoop)
{
    ForEachLoop foreachloop = ex as ForEachLoop;
}
else if (ex is Package)
{
    Package package = ex as Package;
}
//else is taskhost of some kind:
else if (ex is TaskHost)
{
    TaskHost th = ex as TaskHost;
}

The Sequence, For and ForEach classes correspond to their GUI equivalents. The Package class itself is derived from the Executable, but obviously we wouldn’t encounter it in this context. The last, TaskHost, is a generic container class that describes any single-task Control Flow. The TaskHost class’ InnerObject property returns an object (of type object) which can be recast into one of the specific SSIS task classes. This makes it much easier to access task-specific properties and methods. Note that it isn’t necessary, however; we could also just access the TaskHost’s Properties key/value store, but we’d have to know the keys to look up.

Iterating through an existing package’s Control Flows is very straightforward: examine each of the Package’s Executables, and check if it’s a container class. If it is, recast and check its children:

public List<Executable> getAllControlFlowObjects(Executables executables)
{
    List<Executable> foundexecutables = new List<Executable>();

    foreach (Executable ex in executables)
    {
        foundexecutables.Add(ex);

        if (ex is Sequence)
        {
            Sequence seq = ex as Sequence;
            foundexecutables.AddRange(getAllControlFlowObjects(seq.Executables));
        }
        else if (ex is ForLoop)
        {
            ForLoop forloop = ex as ForLoop;
            foundexecutables.AddRange(getAllControlFlowObjects(forloop.Executables));
        }
        else if (ex is ForEachLoop)
        {
            ForEachLoop foreachloop = ex as ForEachLoop;
            foundexecutables.AddRange(getAllControlFlowObjects(foreachloop.Executables));
        }
    }

    return foundexecutables;
}

getAllControlFlowObjects(pkg.Executables);

Creation Names:
The above example used a name string with the STOCK
moniker. Many of the components (but not all) are covered by STOCK, and the name string won’t change between SSIS versions. That is, “STOCK:BulkInsertTask” will work in 2005 up through the present.

Since the Containers are all STOCK, it makes sense to handle them in this way:

  • STOCK:FORLOOP //For Loop
  • STOCK:FOREACHLOOP //For Each Loop
  • STOCK:SEQUENCE //Sequence

If we want to create a Control Flow component that isn’t “stock” or analyze existing components, we need to know the component’s “long name”, which can be retrieved from the component’s Assembly Qualified Name:

//using Microsoft.SqlServer.Dts.Tasks.BulkInsertTask;
string name = typeof(Microsoft.SqlServer.Dts.Tasks.BulkInsertTask.BulkInsertTask).AssemblyQualifiedName;

Each Control Flow specific class is in its own namespace. Here’s the namespaces we’ll need:

  • Microsoft.DataTransformationServices.Tasks.DTSProcessingTask.ASExecuteDDLTask //Analysis Services Execute DDL Task
  • Microsoft.DataTransformationServices.Tasks.DTSProcessingTask.DTSProcessingTask //Analysis Services Processing Task
  • Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceBackupTask //Back Up Database
  • Microsoft.SqlServer.Dts.Tasks.BulkInsertTask.BulkInsertTask // Bulk Insert
  • Attunity.SqlServer.CDCControlTask.CdcControlTask //CDC Control
  • Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceCheckIntegrityTask //Check Database Integrity
  • Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe //Data Flow
  • Microsoft.SqlServer.Dts.Tasks.DMQueryTask.DMQueryTask //Data Mining Query
  • Microsoft.SqlServer.Dts.Tasks.DataProfilingTask.DataProfilingTask //Data Profiling
  • ??? //Execute Package
  • Microsoft.SqlServer.Dts.Tasks.ExecuteProcess.ExecuteProcess //Execute Process
  • Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask //Execute SQL
  • Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceExecuteAgentJobTask //Execute SQL Server Agent Job
  • Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceTSQLExecuteTask //Execute T-SQL Statement
  • Microsoft.SqlServer.Dts.Tasks.ExpressionTask.ExpressionTask //Expression
  • Microsoft.SqlServer.Dts.Tasks.FileSystemTask.FileSystemTask // File System
  • Microsoft.SqlServer.Dts.Tasks.FtpTask.FtpTask // FTP
  • Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceHistoryCleanupTask //History Cleanup
  • Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceFileCleanupTask //Maintenance Cleanup
  • Microsoft.SqlServer.Dts.Tasks.MessageQueueTask.MessageQueueTask, Microsoft.SqlServer.MSMQTask //Message Queue
  • Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceNotifyOperatorTask //Notify Operator
  • Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceReindexTask //Rebuild Index
  • Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceDefragmentIndexTask //Reorganize Index
  • Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask // Script
  • Microsoft.SqlServer.Dts.Tasks.SendMailTask.SendMailTask // Send Mail
  • Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceShrinkTask //Shrink Database
  • Microsoft.SqlServer.Dts.Tasks.TransferDatabaseTask.TransferDatabaseTask // Transfer Database
  • Microsoft.SqlServer.Dts.Tasks.TransferErrorMessagesTask.TransferErrorMessagesTask // Transfer Error Messages
  • Microsoft.SqlServer.Dts.Tasks.TransferJobsTask.TransferJobsTask // Transfer Jobs
  • Microsoft.SqlServer.Dts.Tasks.TransferLoginsTask.TransferLoginsTask // Transfer Logins
  • Microsoft.SqlServer.Dts.Tasks.TransferStoredProceduresTask.TransferStoredProceduresTask // Transfer Master Stored Procedures
  • Microsoft.SqlServer.Dts.Tasks.TransferSqlServerObjectsTask.TransferSqlServerObjectsTask // Transfer SQL Server Objects
  • Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceUpdateStatisticsTask //Update Statistics
  • Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask // WQeb Service
  • Microsoft.SqlServer.Dts.Tasks.WmiDataReaderTask.WmiDataReaderTask // WMI Data Reader
  • Microsoft.SqlServer.Dts.Tasks.WmiEventWatcherTask.WmiEventWatcherTask // WMI Event Watcher
  • Microsoft.SqlServer.Dts.Tasks.XMLTask.XMLTask, Microsoft.SqlServer.XMLTask // XML

Notice that the Execute Package doesn’t have a corresponding class – unfortunately we can’t use this method to obtain the Creation Name for it.
This works well for creating (most) Control Flow components, but for examining an existing package, this won’t be very efficient – we’d have to create an instance of every class’ type, get the creation name, and check it against the Control Flow component that’s in the package. So the final technique is to just write down the name strings and use them directly.
[Note: to obtain the Execute Package name, create the Control Flow using the SSIS Designer, save the package as XML, and examine the document]
With the long Creation Name, we do have to worry about versioning. The following are the long name strings from SSIS 2012:

  • “Microsoft.DataTransformationServices.Tasks.DTSProcessingTask.ASExecuteDDLTask, Microsoft.SqlServer.ASTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Analysis Services Execute DDL Task
  • “Microsoft.DataTransformationServices.Tasks.DTSProcessingTask.DTSProcessingTask, Microsoft.SqlServer.ASTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Analysis Services Processing Task
  • “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceBackupTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Back Up Database
  • “Microsoft.SqlServer.Dts.Tasks.BulkInsertTask.BulkInsertTask, Microsoft.SqlServer.BulkInsertTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // Bulk Insert
  • “Attunity.SqlServer.CDCControlTask.CdcControlTask, Attunity.SqlServer.CDCControlTask, Version=1.0.0.0, Culture=neutral, PublicKeyToken=aa342389a732e31c” //CDC Control
  • “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceCheckIntegrityTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Check Database Integrity
  • “SSIS.Pipeline.3” //Data Flow
  • “Microsoft.SqlServer.Dts.Tasks.DMQueryTask.DMQueryTask, Microsoft.SqlServer.DMQueryTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Data Mining Query
  • “Microsoft.SqlServer.Dts.Tasks.DataProfilingTask.DataProfilingTask, Microsoft.SqlServer.DataProfilingTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Data Profiling
  • “SSIS.ExecutePackageTask.3” //Execute Package
  • “Microsoft.SqlServer.Dts.Tasks.ExecuteProcess.ExecuteProcess, Microsoft.SqlServer.ExecProcTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Execute Process
  • “Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Execute SQL
  • “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceExecuteAgentJobTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Execute SQL Server Agent Job
  • “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceTSQLExecuteTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Execute T-SQL Statement
  • “Microsoft.SqlServer.Dts.Tasks.ExpressionTask.ExpressionTask, Microsoft.SqlServer.ExpressionTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Expression
  • “Microsoft.SqlServer.Dts.Tasks.FileSystemTask.FileSystemTask, Microsoft.SqlServer.FileSystemTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // File System
  • “Microsoft.SqlServer.Dts.Tasks.FtpTask.FtpTask, Microsoft.SqlServer.FtpTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // FTP
  • “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceHistoryCleanupTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //History Cleanup
  • “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceFileCleanupTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Maintenance Cleanup
  • “Microsoft.SqlServer.Dts.Tasks.MessageQueueTask.MessageQueueTask, Microsoft.SqlServer.MSMQTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Message Queue
  • “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceNotifyOperatorTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Notify Operator
  • “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceReindexTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Rebuild Index
  • “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceDefragmentIndexTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Reorganize Index
  • “Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // Script
  • “Microsoft.SqlServer.Dts.Tasks.SendMailTask.SendMailTask, Microsoft.SqlServer.SendMailTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // Send Mail
  • “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceShrinkTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Shrink Database
  • “Microsoft.SqlServer.Dts.Tasks.TransferDatabaseTask.TransferDatabaseTask, Microsoft.SqlServer.TransferDatabasesTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // Transfer Database
  • “Microsoft.SqlServer.Dts.Tasks.TransferErrorMessagesTask.TransferErrorMessagesTask, Microsoft.SqlServer.TransferErrorMessagesTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // Transfer Error Messages
  • “Microsoft.SqlServer.Dts.Tasks.TransferJobsTask.TransferJobsTask, Microsoft.SqlServer.TransferJobsTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // Transfer Jobs
  • “Microsoft.SqlServer.Dts.Tasks.TransferLoginsTask.TransferLoginsTask, Microsoft.SqlServer.TransferLoginsTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // Transfer Logins
  • “Microsoft.SqlServer.Dts.Tasks.TransferStoredProceduresTask.TransferStoredProceduresTask, Microsoft.SqlServer.TransferStoredProceduresTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // Transfer Master Stored Procedures
  • “Microsoft.SqlServer.Dts.Tasks.TransferSqlServerObjectsTask.TransferSqlServerObjectsTask, Microsoft.SqlServer.TransferSqlServerObjectsTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // Transfer SQL Server Objects
  • “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceUpdateStatisticsTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Update Statistics
  • “Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask, Microsoft.SqlServer.WebServiceTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // WQeb Service
  • “Microsoft.SqlServer.Dts.Tasks.WmiDataReaderTask.WmiDataReaderTask, Microsoft.SqlServer.WMIDRTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // WMI Data Reader
  • “Microsoft.SqlServer.Dts.Tasks.WmiEventWatcherTask.WmiEventWatcherTask, Microsoft.SqlServer.WMIEWTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // WMI Event Watcher
  • “Microsoft.SqlServer.Dts.Tasks.XMLTask.XMLTask, Microsoft.SqlServer.XMLTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // XML

Wiring it together:
Each of the Control Flow Containers and the Package class has a PreecedenceConstraints collection, which controls the workflow of the Control Flow space. While we could create a PrecedenceConstraint, and then join it into the collection, it’s much easier to simply use the Add method:

public override void SetFlow(ControlFlow source, ControlFlow destination)
{
    Executable sourceEx = source.Ex;
    Executable destEx = destination.Ex;
    PrecedenceConstraints constraints = pkg.PrecedenceConstraints;
    PrecedenceConstraint seqConstraint = constraints.Add(sourceEx, destEx);

}

PrecedentConstraints themselves are more complicated than shown here, but most people leave the default properties (simple constraint, progress upon success, logical AND) so we’ll leave that for another post.

Leave a Reply

Your email address will not be published. Required fields are marked *