Executive Summary: DTLoggedExec is used to configure and execute SQL Server Integration Services (SSIS) packages and is meant to be used in place of SQL Server’s native DTExec command-prompt utility. DTLoggedExec’s robust logging lets you quickly troubleshoot SQL Server Integration Services (SSIS) packages without having to use Microsoft Visual Studio’s debug mode.

An alternative to the DTExec command-prompt utility, DTLoggedExec is used to configure and execute SQL Server Integration Services (SSIS) packages. DTLoggedExec provides all the package execution and configuration features that DTExec does, such as connections, properties, variables, and progress indicators, but with the addition of robust logging of each step executed by an SSIS package.

DTLoggedExec was written by Davide Mauri, a mentor at Solid Quality Mentors who lives in Italy. Davide wrote the tool to facilitate troubleshooting and logging for high-speed data extraction, transformation, and loading (ETL) operations in an easy and ad hoc method supported by the native DTExec utility. Let’s take a look at the logging options DTLoggedExec offers.

Determine Why Packages Aren’t Executing Properly

DTLoggedExec is intended to compensate for some of the limitations of the native DTExec utility. DTLoggedExec provides a robust and intuitive logging infrastructure that can help you quickly understand how and why a package that has always run correctly suddenly isn’t working properly.

You can start DTLoggedExec from the command prompt. Starting the program from the command prompt lets you enable or disable logging to a text file. Although DTExec has this capability, it’s cumbersome and requires you to code all packages to support logging before it’s needed. DTLoggedExec does away with that requirement.

DTLoggedExec lets you view all the information that’s created while running a package without having to use Microsoft Visual Studio’s debug mode. This capability makes fully logged SSIS processing and troubleshooting much easier and supportable on production servers in which debug mode might not typically be allowed. In addition, you can choose which events you want DTLoggedExec to log.

You can also use DTLoggedExec to profile Data Flow tasks within an SSIS package. This feature lets you log the number of rows processed by the Data Flow task, the number of rows sent as I/O, the start and end times of the Data Flow task, and the number of times the Data Flow task has been executed (e.g., when it’s called in a loop). DTLoggedExec also logs all variable values, the values of properties bound to expressions, and all the properties and related connection properties of erroneous tasks. DTLoggedExec lets you log to its console, a text file, or a database table by defining your own log providers.

System Configuration

DTLoggedExec was developed using both SQL Server 2008 and SQL Server 2005. It runs on Windows Server 2008, Windows Vista, Windows Server 2003, and Windows XP. It was tested on SQL Server 2008 and SQL Server 2005 in 32-bit and 64-bit environments. It has not been tested on—nor is it expected to work with—SQL Server 2000. DTLoggedExec requires .NET Framework 2.0 and the SSIS engine.

DTLoggedExec
BENEFITS: DTLoggedExec logs each step that’s executed by an SSIS package, making it easier to determine why a package isn’t executing correctly.
SYSTEM REQUIREMENTS AND NOTES: SQL Server 2008 or 2005; the SSIS engine; .NET Framework 2.0
HOW TO GET IT: You can download DTLoggedExec from CodePlex at www.codeplex.com/DTLoggedExec/Release/ProjectReleases.aspx.