What is Logging in SSIS ( SQL Server Integration Services ) | A Complete Guide with Best Practices

• In this article you will learn:
• 1.Introduction
• 2.Logging Providers
• 3.Log Events
• 4.Conclusion

Introduction:

Logging can be enabled for all or some tasks and containers as well as for all or some events. Settings from parent containers can be inherited by tasks and containers.Multiple logs can be configured and a task or event can log to any or all of them. Controlling which pieces of information are recorded for any event is also possible.

Logging Providers:

SSIS comes with a number of default log providers. These providers are chosen from a Provider type combo box and are described below:

SSIS Log Provider for Text Files:

This provider saves the log data to the file system as a CSV file. It must configure a File Connection object that describes the file’s location. The simplest way to keep track of a package’s execution is to save log information in a text file. Text files are portable and the CSV format is an easy-to-use industry standard.

SSIS Log Provider for SQL Server Profiler:

• The SQL Profiler trace file is generated by this provider. The file must have the trc file extension in order to be opened with the SQL Profiler diagnostic tool. DBAs can easily view log information by using SQL Profiler trace files.
• Profiler allows you to view the execution of the package step by step even replaying steps in a test environment.

SSIS Log Provider for SQL Server:

• This provider sends package log events to a table in a SQL Server database of your choice.
• An OLE DB Connection is used to demonstrate the database. When you run this package for the first time it creates a table called a syssislog.
• Storing log information in the SQL Server database inherits the benefits of a relational database system’s persisting information. And it is simple to retrieve log information for analysis across multiple package executions.

SSIS Log Provider for Windows Event Log:

• This provider transmits log data to the Application event store. The entries that were created have the Source name SQLISPackage110.
• This provider requires no additional configuration. Logging package execution to the Windows event log is the simplest way to store log events.
• The Windows event log is simple to read and can be accessed remotely if necessary.

SSIS Log Provider for XML files:

• This provider saves log data to a file system in the form of an XML file. To specify the file, use the File Connection object.
• Make sure the file is saved with the xml extension. When logging events to an XML the benefits of the XML specification are inherited.
• XML files can be validated against a schema definition and are more portable across systems.

Log Events:

Once a log provider has been configured it must explain which events in the package should be logged. This is done in the Configure SSIS Logs dialog’s Details tab.Check the box next to an event’s name to enable it to be logged. For example the OnError event for a package has been chosen for logging.Additional events can be selected at the individual task or Data Flow event level by selecting the other containers on the left side of the dialogue. Check the box in the header row of a table to select all events at once.Individual containers in the tree view on the left can be configured to log events at the task level. When logging is configured at the task level, special events exposed by the task can also be logged.Working with the previously created package allows you to see how SSIS logging works.

1. Open one of the packages created earlier in this Topic or any package containing multiple Control Flow Tasks.

2. Open the Configure SSIS Logs dialogue by selecting SSIS Logging from a menu. To enable logging, in the left pane first check the box next to the package name in this case the package is called “10Logging.”

3. To get started a log providers must be explained at the package level. Select package in a TreeView control on the left (the top level) so that the package is more highlighted.

4. In a Provider type dropdown list choose which type of a provider would like to configure; as an example, choose a SSIS Log Provider for XML files. To add a provider to the list click the Add button.. Click dropdown under a Configuration and choose .

5. Once a File Connection Manager Editor opens, set Usage Type property to Create a File. Type C:ProSSISFilesLog.xml as a path for the XML file or click Browse to find a path for XML file location .

6. Click OK to accept a configuration and dismiss dialog. In a Configure SSIS Logs dialog and should now see a new log provider and its properties. Check a box next to new logging provider to enable it at a package level. At this point can give the log provider a descriptive name if want.

7. Click a Details tab to view a list of events can log. If click Advanced will also see a list of the possible fields .

8. Choose OnPreExecute, OnPostExecute and OnError events. Notice that all of fields are automatically chosen. Can uncheck any fields for which are don’t think information will be useful.

9. Move back to a Providers and Logs tab. When checked a log provider at the package level (by checking a checkbox at the highest level in the tree view of a left pane) enabled that log for all the components in the package that are set to inherit a settings from their parent container.

10. When satisfied with a logging settings click OK to close a dialog. If view the Properties window of a task or a container and will find the LoggingMode property. This property can be set to be a UseParentSetting Enabled or Disabled and will match a settings are just configured.

11. Run package. Once the package execution has been completed open a log file to view the XML .Setting up a logging for a package can be as a complicated or as simple as required. It’s possible that are may want to log similar information such as OnError event for all packages.If so can save a settings as a template by clicking Save on a Details tab of a Configure SSIS Logs dialog. Alternatively can load a previously saved a template by clicking a Load button.

Conclusion:

SSIS contains the built-in logging features that capture execution details about a packages.Logging enables to record information about events are interested in as a package runs. The logging information can be saved in a text or XML file to a SQL Server table to Windows event log or to a file suitable for a Profiler.