Lesson 3: Adding Logging

Applies To: SQL Server 2016 Preview

Microsoft Integration Services includes logging features that let you troubleshoot and monitor package execution by providing a trace of task and container events. The logging features are flexible, and can be enabled at the package level or on individual tasks and containers within the package. You can select which events you want to log, and create multiple logs against a single package.
Logging is provided by a log provider. Each log provider can write logging information to different formats and destination types. Integration Services provides the following log providers:

• Text file
• SQL Server Profiler 
• Windows Event log
• SQL Server 
• XML file

In this lesson, you will create a copy of the package that you created in Lesson 2: Adding Looping. Working with this new package, you will then add and configure logging to monitor specific events during package execution. If you have not completed any of the previous lessons, you can also copy the completed Lesson 2 package that is included with the tutorial.

Important

This tutorial requires the AdventureWorksDW2012 sample database. For more information about how to install and deploy AdventureWorksDW2012, Reporting Services Product Samples on CodePlex Step 1: Copying the Lesson 2 Package Applies To: SQL Server 2016 Preview In this task, you will create a copy of the Lesson 2.dtsx package that you created in Lesson 2. Alternatively, you can add the completed lesson 2 package that is included with the tutorial to the project, and then copy it instead. You will use this new copy throughout the rest of Lesson 3. To create the Lesson 3 package If SQL Server Data Tools is not already open, click Start, point to All Programs, point to Microsoft SQL Server 2012, and then click SQL Server Data Tools. On the File menu, click Open, click Project/Solution, select SSIS Tutorial and click Open, and then double-click SSIS Tutorial.sln. In Solution Explorer, right-click Lesson 2.dtsx, and then click Copy. In Solution Explorer, right-click SSIS Packages, and then click Paste. By default, the copied package is named Lesson 3.dtsx. In Solution Explorer, double-click Lesson 3.dtsx to open the package. Right-click anywhere in the background of the Control Flow tab and click Properties. In the Properties window, update the Name property to Lesson 3. Click the box for the ID property, and then in the list, click <Generate New ID>. To add the completed Lesson2 package Open SQL Server Data Tools (SSDT) and open the SSIS Tutorial project. In Solution Explorer, right-click SSIS Packages, and click Add Existing Package. In the Add Copy of Existing Package dialog box, in Package location, select File system. Click the browse (…) button, navigate to Lesson 2.dtsx on your machine, and then click Open. To download all of the lesson packages for this tutorial, do the following. Navigate to Integration Services Product Samples Click the DOWNLOADS tab. Click the SQL2012.Integration_Services.Create_Simple_ETL_Tutorial.Sample.zip file. Copy and paste the Lesson 3 package as described in steps 3-8 in the previous procedure. Step 2: Adding and Configuring Logging Applies To: SQL Server 2016 Preview In this task, you will enable logging for the data flow in the Lesson 3.dtsx package. Then, you will configure a Text File log provider to log the PipelineExecutionPlan and PipelineExecuteTrees events. The Text Files log provider creates logs that are easy to view and easily transportable. The simplicity of these log files makes these files especially useful during the basic testing phase of a package. You can also view the log entries in the Log Events window of SSIS Designer. To add logging to the package On the SSIS menu, click Logging. In the Configure SSIS Logs dialog box, in the Containers pane, make sure that the topmost object, which represents the Lesson 3 package, is selected. On the Providers and Logs tab, in the Provider type box, select SSIS log provider for Text files, and then click Add. Integration Services adds a new Text File log provider to the package with the default name SSIS log provider for text files. You can now configure the new log provider. In the Name column, type Lesson 3 Log File. Optionally, modify the Description. In the Configuration column, click <New Connection> to specify the destination to which the log information is written. In the File Connection Manager Editor dialog box, for Usage type, select Create file, and then click Browse. By default, the Select File dialog box opens the project folder, but you can save log information to any location. In the Select File dialog box, in the File name box type TutorialLog.log, and click Open. Click OK to close the File Connection Manager Editor dialog box. In the Containers pane, expand all nodes of the package container hierarchy, and then clear all check boxes, including the Extract Sample Currency Data check box. Now select the check box for Extract Sample Currency Data to get only the events for this node. Important If the state of the Extract Sample Currency Data check box is dimmed instead of selected, the task uses the log settings of the parent container and you cannot enable the log events that are specific to the task. On the Details tab, in the Events column, select the PipelineExecutionPlan and PipelineExecutionTrees events. Click Advanced to review the details that the log provider will write to the log for each event. By default, all information categories are automatically selected for the events you specify. Click Basic to hide the information categories. On the Provider and Logs tab, in the Name column, select Lesson 3 Log File. Once you have created a log provider for your package, you can optionally deselect it to temporarily turn off logging, without having to delete and re-create a log provider. Click OK. Step 3: Testing the Lesson 3 Tutorial Package Applies To: SQL Server 2016 Preview In this task, you will run the Lesson 3.dtsx package. When the package runs, the Log Events window will list the log entries that are written to the log file. After the package finishes execution, you will then verify the contents of the log file that was generated by the log provider. Checking the Package Layout Before you test the package you should verify that the control and data flows in the Lesson 3 package contain the objects shown in the following diagrams. The control flow should be identical to the control flow in lesson 2. The data flow should be identical to the data flow in lessons 1 and 2. Control Flow Data Flow To run the Lesson 4 tutorial package On the SSIS menu, click Log Events. On Debug menu, click Start Debugging. After the package has completed running, on the Debug menu, click Stop Debugging. To examine the generated log file Using Notepad or any other text editor, open the TutorialLog.log file. Although the semantics of the information generated for the PipelineExecutionPlan and PipelineExecutionTrees events are beyond the scope of this tutorial, you can see that the first line lists the information fields specified in the Details tab of the Configure SSIS Logs dialog box. Moreover, you can verify that the two events that you selected, PipelineExecutionPlan and PipelineExecutionTrees, have been logged for each iteration of the Foreach Loop.