Davide Mauri Blog

Experiences with SQL Server

SSIS Logging: DTLoggedExec

After using a SSIS for quite a lot I decided to develop a DTExec on my own. The reasons can be found in the feature set that I have I mind and I'd like to implement asap:

  1. Ability to switch on/off logging from the execution utility, not only from inside the package (and thus only using Visual Studio)
  2. Avoid to use native log provider and add a personalized concept of logging provider where you can log a LOT more of information. Above all I'm interested in logging variables values for each trappable event. This will make things a lot more easier when you have to understand why a package didn't run correctly.
  3. Ability to save information on task hosts relationships, so that you can understand clearly if a task is inside a container, or if a package is called from another package. All this information must go to the log store so that you can see it in a graphical format each time you need it.
  4. Ability to specify to log ANY of the properties you have in package and package's containers. In this way you can see, for example, how an expression has modified a property.

After a little development and after having solved some problems (mostly due to lack of information in documentation), I've got something that is useful. I've wrote a console application called DTLoggedExec that behaves like DTExec (though is not yet 1:1 compatible with its command options). For now it "just" allows you to decide what kind of Log Provider to use and to log variables values.

Log providers ARE NOT the log providers shipped with SSIS, but are new classes written from the scratch; they allow the DTLoggedExec to support a plugin architecure.

This is a sample screenshot where you can see the variables logged to the console.

As soon as I'll have other news I'll post it ASAP.

Just one thing. I've not jet decided if I'll realese this tool for free or not, so, for now, no executable and no sources are avaiable (Mostly beacause the product, though already used in some production enviroment, is an early alpha version).