Logging in SSIS

Logging is an important part of any data process because it gives administrators and developers insight into what transpired during a process, with the following benefits:

• Error triage to help identify as quickly as possible what was the point and cause of the failure, such as the failure of a Lookup Component to match a record
• Root cause analysis so that a solution can be put in place to prevent a failure situation in the future
• Performance metrics such as package and execution times so that negative performance trends can be observed and addressed before the performance impact causes an ETL failure

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

Logging can be enabled for all or some tasks and containers and for all or any events. Tasks and containers can inherit the settings from parent containers. Multiple logs can be set up, and a task or event can log to any or all logs configured. You can also control which pieces of information are recorded for any event.

Logging Providers

SSIS 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. It 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 Server Profiler: This provider produces a SQL Profiler 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 can 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 OLE DB Connection. The first time this package is executed, a table called sysssislog is created automatically. Storing log information in a SQL Server database inherits the benefits of persisting information in a relational database system. You can 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 are under the Source name SQLISPackage110. 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.

Log Events

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 the Configure SSIS Logs dialog, shown in below screenshot. To enable an event to be logged, check the box next to its name. For instance, in below screenshot, the OnError event for the package has been selected for logging. By selecting other containers on the left side of the dialog, you can select additional events 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. When you configure logging at the task level, the special events exposed by a task can additionally be included in the log.

You can see how SSIS logging works by working with a previously created package.
1. Open one of the packages you created earlier in this Topic or any package with several Control Flow Tasks.
2. From the menu, navigate to SSIS ⇒ Logging to open the Configure SSIS Logs dialog. To enable logging, you must first check the box next to the package name in the left pane (see below screenshot); in this case, the package is named “10Logging.”

NOTE Notice that the checkboxes for the child objects in the package are greyed out. This means that they will inherit the logging properties of the package. You can click in any checkbox to uncheck an object. Clicking again to check the box enables you to set up logging properties specific to that task or container.
3. To get started, the log providers must be defined at the package level. Select package in the TreeView control on the left (the top level) so that the package is highlighted.
4. In the Provider type dropdown list, choose which type of provider you would like to configure; as an example, choose SSIS Log Provider for XML files. Click Add to add the provider to the list. Click the dropdown under Configuration and choose .
5. Once the File Connection Manager Editor opens, set the Usage Type property to Create File. Type C:ProSSISFilesLog.xml as the path for the XML file or click Browse to find the path for the XML file location (see below screenshot).

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

7. Click the Details tab to view a list of events you can log. If you click Advanced, you will also see a list of possible fields (see below screenshot).

8. Choose the OnPreExecute, OnPostExecute, and OnError events. Notice that all of the fields are automatically chosen. You can uncheck any fields for which you don’t think the information will be useful.
9. Move back to the Providers and Logs tab. When you checked the log provider at the package level (by checking the checkbox at the highest level in the tree view of the left pane), you enabled that log for all components in the package that are set to inherit settings from their parent container. Even if that log provider is chosen for an object that does not inherit the log settings, you can use it to select different events and fields. Once you modify the logging on a parent container, such as a For Loop Container, the child objects will inherit from the container, not the package.
10. When you are satisfied with the logging settings, click OK to close the dialog. If you view the Properties window of a task or container, you will find the LoggingMode property. This property can be set to
UseParentSetting, Enabled, or Disabled and will match the settings you just configured.
11. Run the package. Once the package execution has completed, open the log file to view the XML (see below screenshot).

Setting up logging for a package can be as complicated or as simple as required. It’s possible that you may want to log similar information, such as the OnError event, for all packages. If so, you can save the settings as a template by clicking Save on the Details tab of the Configure SSIS Logs dialog. Alternatively, you can load a previously saved template by clicking the Load button.

Frequently Asked SSIS Interview Questions & Answers

Catalog Logging

In addition to logging that can be turned on at a package level, SSIS 2014 introduces another layer of logging as part of the Integration Services catalog. If your SSIS project is set to Project Deployment Model, your deployed packages can utilize the SSIS service. This is especially handy when the package encounters a problem while running in production, rather than during development. In comparison to traditional logging, you do not need to add a logging provider to the package and can apply the catalog logging after the fact. Catalog logging also provides additional information for the administrator after the package has been deployed.

Once your project has been deployed to the server, it will automatically participate in the catalog logging. You can modify the amount of logging that occurs by changing the logging level at execution time. The logging can be set to four levels, as described in the following table.

A variety of tables, views, stored procedures, and built-in reports are set up as part of the SSISDB database on whichever database server your Integration Services catalog is created. If you open Management Studio and go to the Integration Services Catalogs node, you can view the available reports by following these steps:
1. Under the Integration Services Catalogs node, right-click the SSISDB node. If you named your catalog differently, that name will appear here instead.
2. Select Reports ⇒ Standard Reports to see the reports that are available to you, as shown in below screenshot

These reports enable you to see which packages have succeeded or failed or are still running. They break a package down to a more granular level and even show the task or component that could have a problem. For more detailed information on additional reports, please read Administering SSIS Topic.
3. If you select the All Execution report, and then click the Overview link on a package you’ve just run, you will see this granular detail.
By running the last package you created and following these steps, you can see the successes and failures of the package, as shown in below screenshot.

If you need to see the information that is not readily available on the provided reports, you can create your own reports or write your own queries by using the SSISDB database. Below screenshot shows a sample of the views that can be used. By utilizing these views, you can investigate the executions of the packages. For example, the catalog.executions view provides information on the server’s memory information at the start of execution.

4. To create a query that will show you the longest-running objects in your package, you can use the catalog.executable_statistics and catalog.executables views. Always be sure to filter the query on an execution identifier to limit the amount of information returned. below screenshot shows the final query that returns the top five longest-running objects in the package.

The addition of catalog logging combined with the package logging previously discussed makes knowing what happened in your package a breeze. You can pull out any set of information you require to help you make your existing or future packages perform better and succeed more often.

List of Related Microsoft Certification Courses:

SSRS	Power BI
SSAS	SQL Server
SCCM	SQL Server DBA
SharePoint	BizTalk Server
Team Foundation Server	BizTalk Server Administrator