Logging and Reporting Patterns - SQL Server Integration Services Design Patterns - page 374

Server tables, text files, and more. The log information is stored in one object, such as
one file or the sysssislog table.
Each log can be restricted to store only certain types of events, such as OnError ,
OnPreExecute , and OnVariableValueChanged . An administrator can then
look at the logs to see what happened during the execution of the package. Once the
package has been deployed to the server, you cannot change the type or amount of log-
ging that occurs.
Package logging is the best and only option when you're using Integration Services
2005 or 2008 or Integration Services 2012 or 2014 in package deployment mode. In the
project deployment model, you can use package logging on a regular basis to keep
track of errors that may occur or to ensure that packages are executing when expected.
For more in-depth logging and reporting, you will want to use catalog logging and re-
porting, which we will discuss later in this chapter.
Let's take a look at setting up package logging and then how to use the output.
Setting Up Package Logging
To set up logging at a package level, you will go to the package itself and turn on log-
ging. Each package needs to be set up separately to log to the database. You can do this
by right-clicking on the package and selecting the Logging option or by going to the
SSIS menu at the top of the SQL Server Data Tools (SSDT) and selecting the Logging
option.
Within the logging menu, which you can see in Figure 15-1 , you will decide what
type of logging you want to use. Among the options are text files, XML files, and SQL
Server tables. Once you have decided on the type of logging, you will select which
events you want to log and at what level you want to log these events. If you select
events at the highest package level, you will be able to see all events for all lower con-
tainers, too.