Section 8

Troubleshooting SSIS

  • Lesson 47: Logging Package Data
  • Lesson 48: Using Event Handlers
  • Lesson 49: Troubleshooting Errors
  • Lesson 50: Using Data Viewers
  • Lesson 51: Using Breakpoints

Chapter 47

Logging Package Data

Most businesses want to keep track of the packages running on their servers. The logging options in SSIS enable you to record package information at run time. This lesson shows you how to use those built-in logging options. There is a whole new logging ability built into the SSIS catalog covered in Lesson 52.

NOTE Although the logging options built into SSIS are limited, you have other ways to log information. One example is creating Execute SQL Tasks in the event handlers and passing in the system variables. However, creating and maintaining this type of logging can be time-consuming. Software is also available that creates a robust auditing framework on your packages, such as BI xPress from Pragmatic Works, Inc.

To set up logging with the built-in SSIS logging option, right-click in the Control Flow of a package and select Logging. This action opens the Configure SSIS Logs window, as shown in Figure 47-1. To log information from the package you need to place a check next to the package name in the left pane.

Figure 47-1

c47f001.tif

The drop-down menu of provider types has the available logging locations where you can save the information from the package. The options are as follows:

  • Windows Event Log—The Windows Event Log option logs the data to the Windows Event Log.
  • Text File—The Text File option saves the information into a plaintext file with the data comma separated.
  • XML File—The XML File option saves the data in an XML File parsed into XML nodes.
  • SQL Server—The SQL Server option logs the data to a table in your SQL Server. The table logged to on the server is named sysssislog. If it does not exist, this table is created automatically by SSIS when the package runs.
  • SQL Server Profiler—The SQL Server Profiler option logs the data to a file as SQL that can be captured in SQL Server Profiler.

Regardless of the provider type you select, options are available as to what data you want to log and when this data is logged. You can see these options under the Details tab of the Configure SSIS Logs window, as shown in Figure 47-2.

Figure 47-2

c47f002.tif

Under the Details tab, you select the event you want to be logged. If the event occurs during the run of the package, it logs data to the selected provider. The most commonly used events are onError, onWarning, onPreExecute, and onPostExecute.

If you select onError, if an error occurs during the execution of the package, data is stored in the selected log provider. If an error never occurs during the package execution, then no data is logged for this event. The same holds true with onWarning for warnings occurring during the package execution.

The onPreExecute event logs all the data before the package begins execution. This gives you a chance to see what the data values were before the packages performed any tasks. The onPostExecute event option logs all of the data after the package has completed execution.

The data that is saved by the SSIS logging options is shown in the Advanced window, as shown in Figure 47-3, which you bring up in the Details tab by clicking the Advanced button at the bottom of the screen. In the Advanced screen, you can select what data you want to log and on what event. You can select or unselect each item on each event. So if you want to log the computer name, but only on the onPreExecute, you can place a check under the Computer column next to the onPreExecute event only and make sure it is unchecked for the other events. This is the limit of the customization allowed by the built-in SSIS logging.

Figure 47-3

c47f003.tif

To create a log provider, select a provider type from the Provider Type drop-down menu on the Providers and Logs tab in the Configure SSIS Logs window, and then click the Add button. The log provider then appears in the Select the logs to use for the container pane below the drop-down menu. Place a check under the Name column to turn on the log provider. If you want to disable a log, but not remove it from this log menu, you need only remove the check in the Name column next to that log provider.

After you have created the log provider and checked the Name column to activate it, click the Configuration drop-down menu. If a connection type exists in the connection manager that is used by the selected provider, it shows in this window. If the connection does not exist, you can create it by clicking the <New connection…> option.

Next, click the Details tab to choose the events you want to log. If you want to log all events, click the check box next to the Events title at the top of the events list to select all. If you want to select just individual events, place a check next to the event you want to be logged.

After you have selected the events, click the Advanced button and uncheck any data you do not want to log so you don’t have that data saved. You can also save the configuration you have selected to an XML file with the Save button that appears at the bottom of the Details tab. Later you can use this XML file in another package to select the same check boxes in this logging configuration screen automatically by clicking the Load button that also appears at the bottom of the Details tab.

Try It

In this Try It, you create a package with a Script Task that causes an error and logs the package information on the error, before it runs and after it completes. After this lesson, you will have an understanding of how to use SSIS logging to audit the running of your packages.

You can download the completed Lesson47.dtsx from www.wrox.com.

Lesson Requirements

Create a package with a Script Task. Edit the Script Task to open a message box pop-up with a variable that does not exist. Log the package onError, onPreExecute, and onPostExecute events.

Hints

  • You need to turn on three event logs.
  • Do not alter the advanced logging options.

Step-by-Step

1. Create a new package and name it ErrorDemo.
2. Drag a Script Task into the Control Flow.
3. Open the Script Task and set the language to Visual Basic.
4. Click Edit Script and enter the following: 
msgbox(DTS.Variables(“Test”).Value)
WARNING Do not create the “Test” variable. This will cause an error you will capture in the log.
5. Close the Script Editor and click OK in the Script Task.
6. Right-click the Control Flow and select Logging.
7. Place a check next to the package named Error Demo in the left pane.
8. Select the SSIS log provider for text files in the Provider Type drop-down menu and click Add.
9. Place a check in the Name column of the log provider.
10. Click the Configuration drop-down menu and select <New connection…>.
11. Create a connection to a text file named LogTest.txt on your local drive and select create file as the usage type. Then click OK.
12. Click the Details tab.
13. Place a check next to onError, onPreExecute, and onPostExecute.
14. Click OK.
15. Run the package by clicking the green debug arrow on the toolbar.
16. After the Script Task has a red X appear on the task, open the LogTest.txt file on your desktop.
17. You should see the error “Exception has been thrown by the target of an invocation.” This is the error indicating the variable name in the Script Task is invalid.
18. You should also see the pre- and post-execute lines in the text file.
Please select Lesson 47 on the DVD, or online at www.wrox.com/go/ssis2012video, to view the video that accompanies this lesson.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset