DTLoggedExec: Don’t Work with SSIS Without It!

Solid Quality™ mentor and Microsoft SQL Server MVP Davide Mauri recently released an enhanced version of DTLoggedExec, a free tool that logs detailed information about SQL Server 2005 Integration Services (SSIS) execution status and package runtime data.

You can launch SSIS packages through DTExec or SQL Server Agent, but neither technique provides extensive logging information. In addition, you can’t switch logging on or off from the execution tool. If you want to log to a text file, your package must first contain a connection manager that points to the file you want to use. This means that you must engineer your packages from the beginning so that they support the logging infrastructure.

“DTLoggedExec logs a lot of information,” Davide explains. “I wrote the tool because I had some packages that worked perfectly when run during the day but always failed at night. Unfortunately, the native logging support didn’t give me enough information to understand what was going wrong. I needed to know Expression values and Variables values at the time the problem occurred, so I wrote DTLoggedExec. I also had problems with packages that called other packages, and I added some features to handle that particular kind of logging. In the end, I thought that other people might benefit from my work—and maybe even help me—so I released the tool on CodePlex.”

DTLoggedExec, which you can use in place of DTExec, also lets you simply switch on logging when you need it and switch it off when you don’t. “I use DTLoggedExec everywhere I work,” Davide notes, “and it helps me so much that I can’t work with SSIS without it.”

Davide leveraged the power in the Integration Services Object Model to create DTLoggedExec and wrote the tool so that it can easily be expanded via custom log providers called log plugins. The new Version 0.1.3.2, compiled to support both 32-bit and 64-bit platforms, updates the ConsoleLogProvider to add more detailed logging of OnError events, displays the loaded package version, corrects a bug related to SQL Server Authentication, and adds a Help file, sample packages, and usage examples. To download DTLoggedExec and learn more about it, see http://www.codeplex.com/DTLoggedExec.

Davide—an MCP, MCAD, MCDBA, and MCT—is a founding member and mentor of Solid Quality™ Learning Italy and president of User Group Italiano SQL Server (UGISS).