Configuring Package Logging

Now, let's add a little more logging to the package. Drop a Script Task onto the package, right-click on the Control Flow designer, and select Logging. The Configure SSIS Logs dialog box opens, as shown in Figure 11.2.

Figure 11.2. Use the Configure SSIS Logs dialog box to set up package logging

Tip

Make sure you select [Shift+F5] to stop debugging or click the square Stop Debugging toolbar button to enable the Logging menu option. If you're still in debug mode, the designer disables the logging options menu.

Let's configure the package to log to a trace file. Notice the package (Logging) and Script Task nodes in Figure 11.2. Selecting the container in this tree indicates that you want to enable logging for that container. In the designer, put a check mark in the check box for the package and the Script Task. Checking the nodes in this way doesn't fully configure logging; you still need to set up the log destinations and filtering. To do that, click on the package node and select the SQL Profiler type from the Provider Types drop down. Click the Add button. This adds a new SQL Profiler log provider to the package. Next, you need to configure the log provider. This log provider needs a file connection manager, so click on the configuration and the drop down will have a New Connection option. Select that option to create a file connection manager that points to a trace file somewhere on your file system. Make sure the log provider is checked and then click on the Script Task node in the left window. Notice that the dialog box hides the option to create and delete log providers because instances of log providers can only be created at the package level. With the Script Task node selected, make sure that the log provider has a check mark next to it. Your dialog box should look similar to Figure 11.2.

You now have the log providers set up and both the package and the task are configured to send logs to the profiler log provider. Next, let's configure the filters for each of the containers. Click the Details tab on the top of the dialog box next to the Providers and Logs tab. This is where you filter log events. Each selection you make here is specific to the container you have selected in the left window such that the set of selected log events with the Script Task node selected might be different than the set of selected log events for the package. For this example, all the events for both containers are selected. The check box next to the Events label in the header toggles all the events.

Figure 11.3 shows the Details view of the Configure SSIS Logs dialog box.

Figure 11.3. Use the Configure SSIS Logs dialog box to set up package logging

Also, notice that there is an additional log event for the Script Task node called ScriptTaskLogEntry. That's because the Script Task registers the additional log event type. To create log entries of this type, double-click on the Script Task. Select the Script node in the left window and click on the Design Script button. Delete the code comments that say "Add your code here," and in its place, add the following line:

Dts.Log("This is a test log entry", 0, Nothing) 

Now, you are all set up for logging. You have created a SQL Profiler log provider in the package, configured the package and Script Task to log to the SQL profiler trace file, selected the events you want logged from the package and the Script Task, and even added some custom logging from the Script Task. If you run the package now, it will create a few log entries in the trace file you specified in the file connection manager. To view the log, open SQL Profiler from the main SQL Server Start menu under Performance Tools. In SQL Profiler, select File from the main menu and select Open, Trace File or [Ctrl+O] and select the file you previously specified in the file connection manager. Figure 11.4 shows the trace file that was created.

Figure 11.4. SQL Profiler shows the log with the custom log entry

This should give you a pretty good idea how to configure logging. There's also a sample package called Logging.dtsx in the \Samples\S11-Logging sample folder. The package is fairly simple but illustrates how you can configure logging in a package so that different tasks log to different destinations each having its own filter. Try changing the logging settings and filters a bit and see how it affects the logs.

Note

The log providers use connection managers to determine where to create the logs. The connection managers each have an expression to build the ConnectionString and point to the temporary working folder. Look at the SAMPLES::WORKINGFOLDER variable to find where the package creates the log files.