Package Log Providers

Log providers are used to define the destination for the log information that is generated when a package executes. For instance, if you require a record of the execution of your package, a log provider could persist the events and actions that had transpired in a log file, recording not only the execution but also, if required, the values and results of the execution. Defining what should be logged during a package's execution is a two-step process. First, you must define which log providers to use. You can define multiple providers in a single package. The second step is to define what information should be sent to the defined log providers.

To configure logging for a package, open a valid package file and select Tools, and then select SSIS Logging from the main menu. The Configure SSIS Logs dialog box that is displayed shows all the containers that currently exist in the package. The first step is completed by configuring SSIS Log Providers on the Providers and Logs tab, shown in Figure 16-4.

Figure 16-4

SQL Server Integration Services includes several default log providers. These providers are selected in the Provider type combo box and are defined as follows.

• SSIS Log Provider for Text Files: This provider is used to store log information to a CSV file on the file system. This provider requires you to configure a File Connection object that defines the location of the file. Storing log information in a text file is the easiest way to persist a package's execution. Text files are portable and the CSV format is a simple-to-use industry-wide standard.

• SSIS Log Provider for SQL Profiler: This provider produces a SQL Provider trace file. The file must be specified with a trc file extension so that you can open it using the SQL Profiler diagnostic tool. Using SQL profiler trace files is an easy way for DBAs to view log information. Using Profiler, you could view the execution of the package step-by-step, even replaying the steps in a test environment.

• SSIS Log Provider for SQL Server: This provider sends package log events to a table in the specified SQL Server database. The database is defined using an OLEDB Connection. The first time this package is executed, a table called sysdtslog90 will be created automatically. Storing log information in a SQL Server database inherits the benefits of persisting information in a relational database system. You could easily retrieve log information for analysis across multiple package executions.

• SSIS Log Provider for Windows Event Log: This provider sends log information to the Application event store. The entries created will be under the Source name SQLISPackage. No additional configuration is required for this provider. Logging package execution to the Windows Event Log is possibly the easiest way to store log events. The Windows Event Log is easy to view and can be viewed remotely if required.

• SSIS Log Provider for XML Files: This provider stores log information in a specified XML file on the file system. The file is specified through a File Connection object. Make sure you save the file with an xml file extension. Logging events to XML inherits the advantages of the XML specification. XML files are very portable across systems and can be validated against a Schema definition.

Specifying Events to Log

Once you have configured the log providers you wish to employ, you must define what events in the package to log. This is done in the Details tab of Log Configuration dialog box, as shown in Figure 16-5. To enable an event to be logged, check the box next to its name. For instance, in Figure 16-5, the OnError event for the package has been selected to be logged. By selecting other containers on the left-hand side of the dialog box, additional events can be selected down to an individual task or Data Flow event level. To select all events at once, check the box in the header row of the table. By selecting individual containers in the tree view on the left, you can configure the logging of events on an individual task level. By configuring logging at the task level, the special events exposed by a task can additionally be included in the log.

Figure 16-5

Programming Log Providers

The package object exposes the LogProviders collection object, which contains the configured log providers in a package. The LogProvider object encapsulates a provider's configuration information.

The LogProvider object exposes the following key properties.

• Name — A descriptive name for the log provider.

• ConfigString — The name of a valid Connection object within the package that contains information on how to connect to the destination store.

• CreationName — The ProgID of the log provider. This value is used in the creation of log providers dynamically.

• Description — Describes the type of provider and optionally the destination to which it points.

The following example enumerates all the log providers that have been configured in a package and writes the results to the console window:

 public static void GetPackageLogs() {     Application dtsapp = new Application();     Package p = dtsapp.LoadFromDtsServer(@"MSDB\Package6x (1)", "VSTSB2", null);     p.Execute();     Console.WriteLine("LogProviders");     LogProviders logProviders = p.LogProviders;     Console.WriteLine("LogProviders Count: {0}", logProviders.Count);     LogProviderEnumerator logProvidersEnum = logProviders.GetEnumerator();     while (logProvidersEnum.MoveNext())     {         LogProvider logProv = logProvidersEnum.Current;         Console.WriteLine("ConfigString:   {0}", logProv.ConfigString);         Console.WriteLine("CreationName    {0}", logProv.CreationName);         Console.WriteLine("DelayValidation {0}", logProv.DelayValidation);         Console.WriteLine("Description     {0}", logProv.Description);         Console.WriteLine("HostType        {0}", logProv.HostType);         Console.WriteLine("ID              {0}", logProv.ID);         Console.WriteLine("InnerObject     {0}", logProv.InnerObject);         Console.WriteLine("Name            {0}", logProv.Name);         Console.WriteLine("-----------------");     } } 

You can of course dynamically configure a package's log providers. The following C# code snippet details the creation of a SQL Server log provider in a package:

 public static void CreatePackageLogProvider() {     Application dtsapp = new Application();     Package p = dtsapp.LoadFromDtsServer(@"File System\Package6", "VSTSB2", null);     ConnectionManager dbConMgr = p.Connections.Add("OLEDB");     dbConMgr.Name = "VSTSB2.AdventureWorks";     dbConMgr.ConnectionString = "Data Source=VSTSB2;Initial Catalog=AdventureWorks;         Provider=SQLOLEDB.1;Integrated Security=SSPI;Auto Translate=False;";     LogProvider logProvider = p.LogProviders.Add("DTS.LogProviderSQLServer.1");     logProvider.ConfigString = "VSTSB2.AdventureWorks";     logProvider.OpenLog();     p.Execute(); } 

In this example, a valid connection must initially be created to support the communications to the database. Next, the log provider is instantiated by passing the ProgID of the provider you wish to create. The following is a list of the ProgIDs for each type of log provider available:

• Text File Log Provider: DTS.LogProvider.TextFile.1

• SQL Profiler Log Provider: DTS.LogProvider.SQLProfiler.1

• SQL Server Log Provider: DTS.LogProvider.SQLServer.1

• Windows Event Log Provider: DTS.LogProvider.EventLog.1

• XML File Log Provider : DTS.LogProvider.XMLFile.1