The next thing that should be done is to enforce business rules and logical rules on the target database itself. Although doing so is a standard best practice for database design, it’s remarkable how often developers fail to follow this practice. SSIS provides easy mechanisms for logging violations of these rules or constraints. For example, consider the case where the cumulative volume of oil pumped from a well is reset to zero. A simple check constraint on the target field enforces the rule that the cumulative volume for today must be greater than or equal to the cumulative volume for yesterday. Should this test fail, the insert of today’s data will fail. SSIS can then catch and log this failure for this one row of data, while continuing to load data that complies with the business rule. Figure 1 shows an extremely simple example of how this can be handled.
Data is extracted from an OLE DB source to be loaded directly to an OLE DB target. Should an insert fail, the data will be written to a comma delimited file, along with the error code and the error column.
Most frequently, the business process breaks down at this point. You have this data in some type of log, and then what? The best practice is to provide an easy UI that will let a business user address these issues. At a minimum, this UI would provide
- Identifying information that will allow a user to track the offending data to its source.
- A clear reason that the insert or update failed. This will require that you translate the error code. You can view the SQL Server 2008 error codes at msdn.microsoft.com/en-us/library/ms345164.aspx, and the SQL Server 2005 error codes at msdn.microsoft.com/en-us/library/ms345164(SQL.90).aspx. Adding an error description requires a single line of script, as described in the SSIS help topic, Enhancing an Error Output with the Script Component.
- Additional information that will help the user identify the error. For example, simply telling the user "The data value violated the schema constraint" isn’t helpful. You must tell the user why the constraint was violated so that they can do their job to fix the issue.
Audit- and Event-Driven Logging
There will be times when the business requires auditing of data transfers. Most of these requirements can be met with the event-driven logging internal to SSIS. The basic logging screen is relatively simple. Simply select which events to log, as Figure 2 shows.
On the advanced screen, you have more control over what gets logged, as shown in Figure 3.
You should be cautious with SSIS logging. It carries a large I/O overhead and can slow application performance dramatically. Unless absolutely required, I would limit SSIS logging to OnError and OnTaskFail events. I would suggest you limit the details to the computer, source ID, and Message Text details for production purposes. For day-to-day operations, this will provide enough information to troubleshoot failures and raise the errors to a level in which they can be seen and handled.
If you begin to see more routine errors or intermittent errors, or if you’re not getting sufficient data to properly diagnose an issue, it’s best to add additional events to the log slowly. First, you should avoid simply overloading your own diagnostic efforts with too much data. Eventually, someone will need to plow through all of this data to ascertain what’s causing a problem. Second, the logging itself introduces overhead—in some cases substantial overhead—to the SSIS process, and this additional overhead could cause additional problems to arise. I would add the On Warning events and diagnostic events first for errors and bugs. For other issues, such as slow-running loads, you might want to look at the OnProgress, OnPreExecute, and OnPostExecute events as well. Other events can, of course, be used as your environment warrants.
The How
How one should log will vary based your specific situation. Here, I have limited myself to the simplest text file logging. You can also log to any OLE DB–compliant application you choose by selecting the correct tool from the SSIS tool box, or by creating a custom script or class. This is limited only by your imagination.