Section 15.3. Programming SSIS

15.3. Programming SSIS

SSIS lets you programmatically create, load, modify, and execute packages, as well as other objects, including connection managers, log providers, and enumerators. In this section, you'll see how to program SSIS in managed code.

There are two engines that you program against when developing SSIS solutions:

Runtime engine: Manages packages and the execution infrastructure
Data-flow engine: Supports the data-flow task used to extract, transform, and load data

The following two sections contain examples that show how to programmatically perform common tasks when programming against these two engines.

All examples in this section are built using Visual Studio 2005. Each example needs a reference to the Microsoft.SqlServer.ManagedDTS assembly. Additional assembly references are indicated for examples in which they are required.

15.3.1. Control-Flow Programming

Control-flow programming uses the SSIS object model to build packages, add stock and custom tasks, connect tasks, and run packages. Control-flow programming also lets you build objects such as connection managers, variables, log providers, enumerators, event handlers, and configuration files. The following subsections show how.

15.3.1.1. Creating a package

This example creates an empty packagethe top-level container for all other SSIS objects:

     using System;     using Microsoft.SqlServer.Dts.Runtime;     class Program     {         static void Main(string[] args)         {             // create an empty package             Package p = new Package(  );             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     }

The Package class represents the package container and exposes collections of other containers, connections, tasks, log providers, variables, configurations, precedence constraints, and event handlers.

15.3.1.2. Saving a package

This example creates an empty package and saves it to disk using the Application class:

     using System;     using Microsoft.SqlServer.Dts.Runtime;     class Program     {         static void Main(string[] args)         {             // create an empty package             Package p = new Package(  );             // save the package to the File System folder             Application a = new Application(  );             a.SaveToDtsServer(p, null, @"File System\TestPackage", "localhost");             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     }

Results are shown in Figure 15-2.

Figure 15-2. Results for saving package example

After running this example, you can view the package in the Object Explorer window in SQL Server Management Studio. Select View Registered Server Types Integration Services from the main menu. In the Registered Servers window, right-click the SSIS server and select Connect Object Explorer from the context menu. In the Object Explorer window, expand the Stored Packages File System node for the Integration Services instance, as shown in Figure 15-3.

Figure 15-3. Viewing packages using Object Explorer

By default, package (.dtsx) files in the File System node are saved in C:\Program Files\Microsoft SQL Server\90\DTS\Packages. You can open the file using Business Intelligence Studio or you can reload the file programmatically, as described in the next section.

The Application class discovers, accesses, and manages Package objects and provides access to information about the system and available components through its properties.

15.3.1.3. Loading a package

This example loads the package named TestPackage (created in the preceding example) from disk:

     using System;     using Microsoft.SqlServer.Dts.Runtime;     class Program     {         static void Main(string[] args)         {             // load the package to the File System folder             Application a = new Application(  );             Package p = a.LoadFromDtsServer(@"File System\TestPackage",                 "localhost", null);             Console.WriteLine("Package: " + p.CreationName + " " +                 p.Name + " loaded.");             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     }

Results follow in Figure 15-4.

Figure 15-4. Results for loading a package example

15.3.1.4. Adding a task to the package

This example creates a package and adds a ForLoop container and a SendMail task to it. A SQLTask task is added to the ForLoop container.

     using System;     using Microsoft.SqlServer.Dts.Runtime;     class Program     {         static void Main(string[] args)         {             // create an empty package             Package p = new Package(  );             // add a foreach loop             ForLoop fl = (ForLoop)p.Executables.Add("STOCK:ForLoop");             // add a SQL task to the ForLoop             fl.Executables.Add("STOCK:SQLTask");             // add a send mail task             p.Executables.Add("STOCK:SendMailTask");             // enumerate the tasks in the package             foreach (Executable e in p.Executables)             {                 if (e.ToString(  ) == "Microsoft.SqlServer.Dts.Runtime.TaskHost")                 {                     Console.WriteLine(((TaskHost)e).InnerObject.GetType().ToString(  ));                 }                 else if (e.ToString(  ) == "Microsoft.SqlServer.Dts.Runtime.ForLoop")                 {                     // enumerate the tasks in the ForLoop container                     Console.WriteLine(e.ToString(  ));                     foreach (Executable e2 in ((ForLoop)e).Executables)                         Console.WriteLine("  " +                             ((TaskHost)e2).InnerObject.GetType().ToString(  ));                 }             }             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     }

The output is shown in Figure 15-5.

Figure 15-5. Output for adding tasks example

The output from the example enumerates the tasks once after they have been created. Enumerating tasks is discussed in the section "Enumerating task properties," later in this chapter.

You can add tasks to Package, Sequence, ForLoop, ForEachLoop, and DtsEventHandler objectsthese objects are all containers. Each container has an Executables collection containing Executable objects.

You add an object to a container by calling the Add( ) method of the container object. The Remove( ) method removes objects from the container. The Add( ) method takes a single string parameter that contains the CLSID, PROGID, or STOCK moniker, or a CreationName that identifies the task or container. Table 15-8 describes the SSIS tasks.

Table 15-8. SSIS tasks
STOCK name	Description
`ActiveXScriptTask`	Creates and uses ActiveX scripts created in SQL Server 2000. This feature is intended only for backward compatibility and will be removed in the next version of SQL Server.
`BulkInsertTask`	Runs bulk inserts.
`DMQueryTask`	Runs prediction queries based on data-mining models built in Analysis Services.
`Exec80PackageTask`	Runs packages created in SQL Server 2000 as part of a workflow.
`ExecutePackageTask`	Runs other SSIS packages as part of a workflow.
`ExecuteProcessTask`	Runs an application or batch file as part of a workflow.
`FileSystemTask`	Runs a command against the filesystem.
`FtpTask`	Uses FTP to download and upload files and manage directories.
`MessageQueueTask`	Sends or receives messages to and from an MSMQ queue.
`PipelineTask`	Moves data between source and destination with optional transformation.
`ScriptTask`	Runs custom code to perform functions not available using the built-in SSIS tasks.
`SendMailTask`	Sends an email message.
`SQLTask`	Executes T-SQL commands.
`transferDatabaseTask`	Transfers databases from one SQL Server instance to another.
`transferErrorMessagesTask`	Copies user-defined error messages from one SQL Server instance to another.
`transferJobsTask`	Transfers SQL Server Agent jobs from one SQL Server instance to another.
`TRansferLoginsTask`	Transfers logins from one SQL Server instance to another.
`transferSqlServerObjectsTask`	Copies SQL Server objects from one SQL Server instance to another.
`transferStoredProceduresTask`	Copies stored procedures from the master database of one SQL Server instance to another.
`WebServiceTask`	Runs a web method and stores the results in a specified location.
`WmiDataReaderTask`	Configures and runs a WMI query.
`WmiEventWatcherTask`	Runs a WMI query and waits for events associated with the query.
`XMLTask`	Retrieves, manipulates, and saves XML documents stored in files.

The container classes described in Table 15-9 have the same collections as the Package class, letting you nest tasks within the package to an arbitrary depth. You have already encountered the Executables collection; more of the collections will appear throughout the remainder of this chapter.

Table 15-9. SSIS containers
STOCK name	Description
`ForEachLoop`	Defines an iterative workflow using the `for each` iteration element.
`ForLoop`	Defines an iterative workflow using the `for` iteration element.
`Sequence`	Defines a control flow that is a subset of the workflow of the parent container.

15.3.1.5. Adding a connection manager

This example creates a package and adds a SQL Server ADO.NET connection manager to it:

     using System;     using Microsoft.SqlServer.Dts.Runtime;     class Program     {         static void Main(string[] args)         {             // create an empty package             Package p = new Package(  );             Console.WriteLine("Beginning number of connections: " +                p.Connections.Count);             // add a connection to AdventureWorks             ConnectionManager cm;             cm = p.Connections.Add("ADO.NET");             Console.WriteLine("Connection added.");             // configure connection             cm.ConnectionString = "Data Source=localhost;" +                 "Integrated Security=SSPI;Initial Catalog=AdventureWorks";             Console.WriteLine("Ending number of connections: " + p.Connections.Count);             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     }

Results are shown in Figure 15-6.

Figure 15-6. Results for adding connection manager example

The ConnectionManager class represents a connection to a data source. The connection type is specified as an argument to the constructor from the connection types described in Table 15-3. It provides a layer of abstraction so that you can interact with a variety of data sources in a consistent manner. The Connections property of the Package class exposes the collection of connection managers associated with the Package class. The ConnectionInfos property of the Application class returns a collection of information about connections installed on the computer as ConnectionInfo objects.

The runtime engine manages connections for the package as it runs so that you do not need to perform tasks such as opening and closing connections.

15.3.1.6. Running a package

This example creates a package with a SQLTask task and runs the package. You need to add a reference to the Microsoft.SqlServer.SQLTask assembly to build and execute this example.

     using System;     using Microsoft.SqlServer.Dts.Runtime;     using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;     class Program     {         static void Main(string[] args)         {             // create an empty package             Package p = new Package(  );             // add a connection manager             ConnectionManager cm = p.Connections.Add("ADO.NET");             cm.Name = "CM_ProgrammingSqlServer2005";             cm.ConnectionString = "Data Source=localhost;" +                 "Integrated Security=SSPI;Initial Catalog=ProgrammingSqlServer2005";             // add a SQL task to the package             Executable e = p.Executables.Add("STOCK:SQLTask");             TaskHost th = (TaskHost)e;             ExecuteSQLTask est = (ExecuteSQLTask)th.InnerObject;             est.Connection = cm.Name;             est.SqlStatementSourceType = SqlStatementSourceType.DirectInput;             est.SqlStatementSource = "CREATE TABLE TestTable " +                 "( ID int NOT NULL, Description nchar(100))";             // run the package             DTSExecResult r = p.Execute(  );             // check the status and result of the package run             Console.WriteLine("Status: " + p.ExecutionStatus.ToString(  ));             if (r == DTSExecResult.Success)                 Console.WriteLine("Package executed successfully.");             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     }

Results are shown in Figure 15-7.

Figure 15-7. Results for running a package example

Running the package creates the table TestTable in the ProgrammingSqlServer2005 database when the package runs.

The example creates a Package object and a ConnectionManager object containing connection information to the ProgrammingSqlServer2005 database. A SQL task executable is added to the package by passing the string STOCK:SQLTask to the Add( ) method of the Executables collection for the package. The Executable object is cast to a TaskHost object, which is then cast to a ExecuteSQLTask object. The SQL task is configured by setting properties of the ExecuteSQLTask object.

The TaskHost class is a wrapper for a task. It is used to retrieve additional properties of and methods on the task. The InnerObject property of the TaskHost class accesses the task object, and can be cast to the specific type of task.

SSIS executable objects implement an Execute( ) method that runs the executable. The ExecutionStatus property of a container returns a value from the DTSExecStatus enumeration, described in Table 15-10, indicating the status of the task execution.

Table 15-10. DTSExecStatus enumeration
Name	Description
`Abend`	The task terminated abnormally because of an internal error.
`Completed`	The task completed and returned either a `Failure` or `Success` result for the execution result (`DTSExecResult`).
`Executing`	The task is running.
`None`	The task is idle.
`Suspended`	The task is suspended.
`Validating`	The task is validating .

The Execute( ) method of a container returns a value from the DTSExecResult enumeration, described in Table 15-11, indicating the result of executing the package.

Table 15-11. DTSExecResult enumeration
Name	Description
`Canceled`	The task was cancelled.
`Completion`	The task ran to completion.
`Failure`	The task failed.
`Success`	The task succeeded.

15.3.1.7. Validating a package

This example creates and validates a package. You need to add a reference to the Microsoft.SqlServer.SQLTask assembly to build and execute this example.

     using System;     using Microsoft.SqlServer.Dts.Runtime;     using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;     class Program     {         static void Main(string[] args)         {             // create an empty package             Package p = new Package(  );             // add a connection manager             ConnectionManager cm = p.Connections.Add("ADO.NET");             cm.Name = "CM_ProgrammingSqlServer2005";             cm.ConnectionString = "Data Source=localhost;" +                 "Integrated Security=SSPI;Initial Catalog=ProgrammingSqlServer2005";             // add a SQL task to the package             Executable e = p.Executables.Add("STOCK:SQLTask");             TaskHost th = (TaskHost)e;             ExecuteSQLTask est = (ExecuteSQLTask)th.InnerObject;             est.Connection = cm.Name;             est.SqlStatementSourceType = SqlStatementSourceType.DirectInput;             est.SqlStatementSource = "CREATE TABLE TestTable " +                 "( ID int NOT NULL, Description nchar(100))";             // validate the package             DTSExecResult r = p.Validate(p.Connections, p.Variables, null, null);             Console.WriteLine("Validation result: " + r);             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     }

The output is shown in Figure 15-8.

Figure 15-8. Output for validate package example

The Package class and other containers have a Validate( ) method that validates dependencies and settings for the object to verify that it will successfully execute. The method ensures that required values are set and contain appropriate values. When connections are validated, a connection to the data is not actually made and data at the data source is not checked. The Validate( ) method can raise and log events.

15.3.1.8. Enumerating task properties

This example creates a package, adds a transferDatabaseTask task to it, and enumerates the task properties. You need to add a reference to the Microsoft.SqlServer.TransferDatabase assembly to compile and run this example.

     using System;     using Microsoft.SqlServer.Dts.Runtime;     using Microsoft.SqlServer.Dts.Tasks.TransferDatabaseTask;     class Program     {         static void Main(string[] args)         {             // create an empty package             Package p = new Package(  );             Executable e = p.Executables.Add("STOCK:TransferDatabaseTask");             TaskHost th = (TaskHost)e;             TransferDatabaseTask tdt = (TransferDatabaseTask)th.InnerObject;             Console.WriteLine("Type = " + tdt.GetType().ToString(  ));             Console.WriteLine("Version = " + tdt.Version);             Console.WriteLine(  );             foreach (DtsProperty dp in th.Properties)                 Console.WriteLine(dp.Name + " = " + dp.GetValue(th));             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     }

Results are shown in Figure 15-9.

The Properties collection of DtsProperty objects for the TaskHost class exposes a container-specific collection of properties. The property values are accessed by calling the GetValue( ) method of the DtsProperty class.

15.3.1.9. Connecting tasks

This example creates a package, adds two tasks, and sets a constraint that does not allow the second task to execute until the first completes:

     using System;     using Microsoft.SqlServer.Dts.Runtime;     class Program     {         static void Main(string[] args)         {             // create an empty package             Package p = new Package(  );             // create the tasks             Executable e1 = p.Executables.Add("STOCK:TransferDatabaseTask");             Executable e2 = p.Executables.Add("STOCK:SendMailTask");

Figure 15-9. Results for enumerating task properties example

             // create the precedence constraint             PrecedenceConstraint pc = p.PrecedenceConstraints.Add(e1, e2);             pc.Name = "e1 before e2 precedence constraint";             pc.Value = DTSExecResult.Completion;             foreach (PrecedenceConstraint pc1 in p.PrecedenceConstraints)             {                 Console.WriteLine(pc1.Name);                 Console.WriteLine("  From: " +                     ((TaskHost)pc1.PrecedenceExecutable).InnerObject);                 Console.WriteLine("  To:   " +                     ((TaskHost)pc1.ConstrainedExecutable).InnerObject);             }             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     }

Results are shown in Figure 15-10.

Figure 15-10. Results for connecting tasks example

The PrecedenceConstraint class configures precedence between two containers. The Add( ) method for the PrecedenceConstraints collection for a container takes two argumentsthe executable before the constraint and the executable after the constraint. The Value property of the PrecedenceConstraints collection specifies the constraint typea value from the DTSExecResult enumeration described in Table 15-11.

15.3.1.10. Using variables

You can use variables to dynamically set values in packages, containers, tasks, and event handlers. This example adds a user variable to an empty package and iterates over all variables in the package:

     using System;     using Microsoft.SqlServer.Dts.Runtime;     class Program     {         static void Main(string[] args)         {             // create an empty package             Package p = new Package(  );             // create a variable             p.Variables.Add("Variable1", false, "", 1);             foreach (Variable v in p.Variables)                 Console.WriteLine(v.Name + " = " + v.Value +                     " [" + v.DataType + "]");             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     }

The output is shown in Figure 15-11.

Figure 15-11. Output for add variable example

The Variables property of container objects accesses the collection of Variable objects for the container. You can see the variable named Variable1 that was added programmatically in the output.

SSIS provides two default namespaces for variables. The User namespace is the default location for variables created programmatically. The System namespace contains variables that store information about the running package and its objects.

15.3.1.11. Configuring a package

This example creates a configuration file for a new package. You need to create the directory C:\PSS2005\Packages or specify a different location to run the examples in this section.

     using System;     using Microsoft.SqlServer.Dts.Runtime;     class Program     {         static void Main(string[] args)         {             string packagePath = @"C:\PSS2005\Packages\";             // create an empty package             Package p = new Package(  );             // enable configurations             p.EnableConfigurations = true;             p.ExportConfigurationFile(packagePath + "SamplePackageConfig.xml");             // create a variable             Variable v = p.Variables.Add("Variable1", false, "", 1);             // create the configuration file             Configuration   c = p.Configurations.Add(  );             c.ConfigurationString = "SamplePackageConfig.xml";             c.ConfigurationType   = DTSConfigurationType  .ConfigFile;             c.Description = "Sample configuration file";             c.PackagePath = v.GetPackagePath(  );             // save the package with the configuration file to an XML file             Application a = new Application(  );             a.SaveToXml(packagePath + "SamplePackage.xml", p, null);             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     }

Running the example creates the configuration file shown in Figure 15-12.

The Configuration class represents information about how a package is configured. The Configurations class represents a collection of Configuration objects. The Package class exposes its collection of configurations through the Configurations property.

The EnableConfigurations property of the Package class indicates whether a package supports loading configuration files when the package is loaded. If the value of EnableConfigurations is false, the package uses configuration values persisted within the package. The ExportConfigurationFile( ) method of the Package class creates an XML file containing all deployable variables in the package. You can see this in the <DTS:Configuration> element in the XML in Figure 15-12.

The ConfigurationType property of the Configuration class specifies the way in which configuration information is stored. The property takes a value from the DTSConfigurationType enumeration, described in Table 15-12. The ConfigurationString property specifies the location of the configuration.

Table 15-12. DTSConfigurationType enumeration
Value	Description
`ConfigFile`	Configuration file
`EnvVariable`	Environment variable
`IConfigFile`	Environment variable that contains information about the configuration flat file
`IParentVariable`	Environment variable that contains information about the package variable
`IRegEntry`	Environment variable that contains information about the registry entry
`ISqlServer`	Environment variable that contains information about the SQL Server instance
`ParentVariable`	Package variable
`RegEntry`	Registry entry
`SqlServer`	SQL Server `msdb` database

Figure 15-12. Package configuration file example

Finally, the SaveToXml( ) method of the Application class saves the package and configuration information to the SamplePackage.xml file.

This example loads the package created in the preceding example and displays the configuration information:

     using System;     using Microsoft.SqlServer.Dts.Runtime;     class Program     {         static void Main(string[] args)         {             string packagePath = @"C:\PSS2005\Packages\";             Application a = new Application(  );             Package p = a.LoadPackage(packagePath + "SamplePackage.xml", null);             // output the user variables             foreach (Variable v in p.Variables)             {                 if (!v.SystemVariable)                     Console.WriteLine(v.Name + " = " + v.Value);             }             // output the configurations             Console.WriteLine(  );             foreach (Configuration c in p.Configurations)             {                 Console.WriteLine("ConfigurationString = " + c.ConfigurationString);                 Console.WriteLine("ConfigurationType = " + c.ConfigurationType);                 Console.WriteLine("CreationName = " + c.CreationName);                 Console.WriteLine("Description = " + c.Description);                 Console.WriteLine("ID = " + c.ID);                 Console.WriteLine("Name = " + c.Name);                 Console.WriteLine("PackagePath = " + c.PackagePath);             }             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     }

Results are shown in Figure 15-13.

Figure 15-13. Results for loading package configuration file example

15.3.1.12. Handling events

The SSIS runtime provides a set of events that occur before, during, and after the validation and execution of a package, as described in Table 15-4. You can create event handlers that execute a workflow when an event is raised. The events can be captured either by implementing the IDTSEvents interface in a class or by creating a DtsEventHandler object in the workflow.

The first example shows how to use the IDTSEvents interface:

     using System;     using Microsoft.SqlServer.Dts.Runtime;     class Program : DefaultEvents     {         static void Main(string[] args)         {             // create an empty package             Package p = new Package(  );             // ... build the package             // execute the package providing an instance of the Program class             // as an argument             Program program = new Program(  );             DTSExecResult r = p.Execute(null, null, program, null, null);             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }         public override void OnPreExecute(Executable exec, ref bool fireAgain)         {             Console.WriteLine("Event: OnPreExecute");         }     }

The output is shown in Figure 15-14.

Figure 15-14. Output from IDTSEvents example

You can receive event notifications during execution or validation of a container by building a class that implements the IDTSEvents interface. You can also derive a class from the DefaultEvents class and override the events you are interested in handling this is the approach used in this example. You need to create an instance of the Program class and provide it as the third argument to the Execute( ) method of the package to receive event notifications.

The second example creates a DtsEventHandler object and uses it to handle the OnTaskFailed event. You need to add a reference to the Microsoft.SqlServer.SQLTask assembly to compile and run this example.

     using System;     using Microsoft.SqlServer.Dts.Runtime;     using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;     class Program     {         static void Main(string[] args)         {             // create an empty package             Package p = new Package(  );             // add a connection manager             ConnectionManager cm = p.Connections.Add("ADO.NET");             cm.Name = "CM_ProgrammingSqlServer2005";             cm.ConnectionString = "Data Source=localhost;" +                 "Integrated Security=SSPI;Initial Catalog=ProgrammingSqlServer2005";             // add a SQL task to the package             Executable e = p.Executables.Add("STOCK:SQLTask");             TaskHost th = (TaskHost)e;             ExecuteSQLTask est = (ExecuteSQLTask)th.InnerObject;             est.Connection = cm.Name;             est.SqlStatementSourceType = SqlStatementSourceType.DirectInput;             est.SqlStatementSource = "CREATE TABLE TestTable2 " +                 "( ID int NOT NULL, Description nchar(100))";             // add the event handler             DtsEventHandler deh = (DtsEventHandler)p.EventHandlers.Add("OnTaskFailed");             // Add task to fire when the event handler executes             Executable e2 = deh.Executables.Add("STOCK:SQLTask");             TaskHost th2 = (TaskHost)e2;             ExecuteSQLTask est2 = (ExecuteSQLTask)th2.InnerObject;             est2.Connection = cm.Name;             est2.SqlStatementSourceType = SqlStatementSourceType.DirectInput;             est2.SqlStatementSource = "CREATE TABLE TestTable3 " +                 "( ID int NOT NULL, Description nchar(100))";             DTSExecResult r = p.Execute(  );             Console.WriteLine("Status: " + r.ToString(  ));             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     }

You need to run this example twice:

The first run successfully creates a table named TestTable2 in the ProgrammingSqlServer2005 database. The OnTaskFailed event handler never executes, because no task in the package failed when the package was runthe table TestTable3 is not created. Confirm that table TestTable2 exists and table TestTable3 does not exist by using Object Explorer in SQL Server Management Studio.
The second run tries but fails to create the table TestTable2, because it already exists from the previous package run. The failure causes the OnTaskFailed event handler to run, creating TestTable3. Confirm that both TestTable2 and TestTable3 exist.

The DtsEventHandler class represents a container that runs when specific events occur. You create and add workflow for the event handler in the same way as for any other container.

15.3.1.13. Logging

This example enables logging for a package and writes log information to an XML file. You need to create the directory C:\PSS2005\Logs to compile and execute this example.

     using System;     using System.IO;     using Microsoft.SqlServer.Dts.Runtime;     class Program     {         static void Main(string[] args)         {             // create an empty package             Package p = new Package(  );             // add a file connection manager             ConnectionManager cm = p.Connections.Add("FILE");             cm.Name = "Logging";             cm.ConnectionString = @"C:\PSS2005\Logs\TestLog.xml";             // enable logging             p.LoggingMode = DTSLoggingMode.Enabled;             // create a log provider in the package and select it for logging             LogProvider lp = p.LogProviders.Add("DTS.LogProviderXmlFile.1");             lp.ConfigString = cm.Name;             p.LoggingOptions.SelectedLogProviders.Add(lp);             // set the events to include in the logging             p.LoggingOptions.EventFilterKind = DTSEventFilterKind.Inclusion;             p.LoggingOptions.EventFilter =                 new string[] { "OnPreExecute", "OnPostExecute", "OnTaskFailed" };             // run the package             DTSExecResult r = p.Execute(  );             Console.WriteLine("Status: " + r);             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     }

The log file C:\PSS2005\Logs\TestLog.xml is shown in Figure 15-15.

The LogProvider class contains information about a log provider for a container. The LogProviders property of the Package class exposes the collection of all log providers associated with the package. The Add( ) method of the LogProviders class adds the specified log provider to the collection of log providers. The log provider is specified by passing either the ProgID or ClassID of one of the five included log providers or of a custom log provider as the argument to the Add( ) method. Table 15-6 lists included log providers and corresponding ProgIDs. The ConfigString property supplies configuration information specific to the log provider. The XML log provider used in this example and most log providers use the ConfigString property to specify a ConnectionManager object used to connect to the log destination.

The LogProviderInfos property of the Application class returns a collection of information about log providers installed on the computer.

The LoggingMode property of each container specifies whether event information for the container is logged. It takes a value from the DTSLoggingMode enumerationDisabled, Enabled, or UseParentSetting. If LoggingMode is not specified, it defaults to the logging mode of the parent container. The package is the top-level container, and its LoggingMode property defaults to Disabled. Each provider has different configuration options set through the ConfigString property.

The LoggingOptions.EventFilter property of the container takes a string array of events that you want to log. The LoggingOptions.EventFilterKind property takes a value from DTSEventFilterKind indicating whether the array of events is included or excluded from logging. Logging can be further filtered by setting columns in the DTSEventColumnFilter structure to TRue or false to select whether the columns are logged, and assigning the structure to LoggingOptions using the SetColumnFilter( ) method.

15.3.2. Data-Flow Programming

A data flow lets you load, transform, and save data. You build a data flow by adding pipeline componentsdata-flow sources, transformations, and destinationsto the data flow, and then configuring and connecting the components.

Figure 15-15. Results for logging to XML file example

This example enumerates the available pipeline componentsdata-flow sources, transformations, and destinations:

     using System;     using Microsoft.SqlServer.Dts.Runtime;     class Program     {         static void Main(string[] args)         {             Application a = new Application(  );             foreach (PipelineComponentInfo pci in a.PipelineComponentInfos)             {                 Console.WriteLine(pci.Name);                 Console.WriteLine("  " + pci.ComponentType);                 Console.WriteLine("  " + pci.CreationName);                 Console.WriteLine("  " + pci.Description);                 Console.WriteLine(  );             }             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     }

Partial results are shown in Figure 15-16.

Figure 15-16. Results for enumerating pipeline components example

The Application class exposes a PipelineComponentInfos collection. It contains a PipelineComponentInfo object for each installed pipeline component. The ComponentType property of the PipelineComponentInfo class identifies the type of component as a value from the DTSPipelineComponent enumerationDestinationAdapter, SourceAdapter, transform, or View.

Data-flow programmingprogrammatically constructing and configuring a data flow, developing custom source, transformation, and load components that participate in the data flow, and programming custom data-flow componentsis beyond the scope of this chapter. See Microsoft SQL Server 2005 Books Online for more information about these topics.