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 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.