24 Mar

SSIS Logging

Logging in SSIS, for the most part, is a pretty clean process.  You enable it, pick your options, tell it where to go and roll from there, almost.

You enable logging by right-clicking the Control Flow and selecting logging.  You can also enable logging by clicking on SSIS > Logging in the menu.

You can implement logging for 3 things: packages, containers and tasks.  All of these have different options so they are distinct in that the sense that a FTP Task will have different detail options than a Data Flow task.  You enable logging for a container in your package by checking the box next to the it in the Container window.

There are 5 logging providers.  They are,

  • SQL Server
  • SQL Server Profiler – It’s flagged in the official documentation that you can’t use this if the package runs in 64-bit mode.
  • Text File
  • Windows Event Log
  • XML File

At this point, especially if you’ve plowed any of my other posts, you should know what those are.  You enable a provider by selecting it in the provider type box, clicking add, then checking it in the window.  It’s perfectly acceptable to select different providers for different packages and containers.  You just need to add it first.  Also, a provider can only be added when you have the package box highlighted.

The configuration column is simply a pointer to the file or database if there is one for the provider.

Providers and logs.

Providers and logs.

The Details tab list events that you want to track for a given container.  There are far too many to list and many packages have different events that they track.  If Microsoft wants me to know them for the 70-463 exam then I’m toast.  I’m not memorizing all of these.

Further, there is an Advanced button, which allows you to refine the information which you want to be written to the log.  For instance, computer, operator, message text and more.  These are customizable for each event you select to log.

The short version of the logging process is:

  1. Enable Logging (right-click Control Flow or SSIS > Logging).
  2. Select provider(s) and click Add from the package level.
  3. Check the Container that you want to log.
  4. Check the provider(s) that you wish to use for that container.
  5. Click the Details tab to enable events that you would like to log.
  6. Click the Advanced button to enable/disable information to be included in the log.

And that is pretty much it.  There is a lot to potentially memorize but I’m going to gamble that I won’t have to enable the FTPOperation event and log the Computer and Operator information.

Other items of note.

You can enable logging with dtexec.  You probably want to know that.

Events that you can log.

Details tab.

You can set a container to use the parent’s logging options.  You enable this by setting the LoggingMode property to UseParentSetting.  This also sounds like something you might be asked.

You can create templates for logging.  I’m not going into detail on this other than to say you can do it and the templates are stored in XML files.

You can read more here.

http://msdn.microsoft.com/en-us/library/ms140246.aspx