All Integration Services tasks and containers support a default set of events for logging. These events typically represent different stages in the "lifetime" of the object at run time, such as "OnPreExecute", "OnPostExecute", and "OnError".
Many tasks also support custom log entries that may be of more interest to you than the generic events. You can find a list here in BOL (although I see that the list isn't 100% up to date): Custom Messages for Logging. A handful of the interesting custom log entries are:
- The package object's Diagnostic event, which lets you log calls to external data providers to troubleshoot connectivity issues. See Troubleshooting Package Connectivity.
- The Execute SQL task's ExecuteSQLExecutingQuery, which includes the text of the SQL statement that you may have concatenated at run time by using an expression.
When you first open the Configure SSIS Logs dialog box by selecting Logging on the SSIS menu in BIDS, you see something like this:
Notice the gray (not black) check mark next to the Data Profiling Task in our example. You may be tempted to interpret this check mark to mean, "All available items will be logged for the Data Profiling Task." In fact, this is not the case. The real meaning of the gray check mark is, "All default items common to all tasks and containers will be logged."
Here's the gotcha: Custom items are NOT logged by default. That is, the task-specific information that may interest you most is NOT logged by default. This behavior may not be what you expect, but the message that's displayed when we select an individual task gives us a hint: "To configure unique logging options for this container, enable logging for it in the tree view."
To begin with, before we touch the individual task, we see this. Checkboxes are grayed out because the task inherits what is logged.
Let's say that we want enable custom logging of the DataProfilingTaskTrace event on the Data Profiling task. The next thing to do is to click the checkbox next to Data Profiling Task. This turns off the default logging for that task, and we see this. Checkboxes are still grayed out because we haven't configured custom logging for the task.
Now click the checkbox next to Data Profiling Task one more time, and we see this. This turns on custom logging for the task, but all available log entries are de-selected. We can select the ones we want individually (including the DataProfilingTaskTrace event), or click the checkbox in the header row to select them all at once.
To summarize, the logging settings for individual tasks cycle through 3 states as you click the checkbox next to each task name:
- Default setting: Gray check mark. Only events common to all tasks are logged.
- No check mark. No events are logged.
- Black check mark. You can select the events, including custom events, that you want to log for this task. No events are selected by default.
If you're a developer of custom components for Integration Services, and you've gone to the trouble of creating custom LogEntryInfos, you may want to remind your customers of this behavior of the Configure SSIS Logs dialog box, or your custom log entry will go to waste.