Facing the Impedance Mismatch News Feed
With the help of these last Holydays, I've been able to release the latest version
of DTLoggedExec, the DTExec replacement tool I've been developing from 2006.
deep logging of Control Flow, this new version brings a very important and powerful
ability to log and thus profile the Dataflow. This means that is it possible to
deeply monitor the performance of ETL through time, also verifying how every change
we do impact on it. Here's some examples that I've used on a customer:
But where's the news here? Creating a Dataflow using RowCount transformations allows
you to do that already! Yeah - I know - but here's the trick: you don't need to
develop your packages using any specific transformation or technique in order to have
such logging and profiling! Just run the package using DTLoggedExec with the
option for profiling and that's it! Logging and profiling out-of-the-box served freshly!
DTLoggedExec is compatible with SQL Server 2005 and SQL Server 2008, and with any
To help people to start to use it, I've created a specific website you can refer to:
For any question don't hesitate to check the FAQ website section or contact me directly!
Executable and source code is hoste on CodePlex, under a Creative Common license:
It's been a long time since I've been able to work consistenly on my DTLoggedExec
tool. Fortunately in these last days I've been able to spare some free time to continue
the work, and now, within a few days I should be able realease a new version of it.
This new version will contain a new (and imho very very important) feature:
DTLoggedExec will be able to profile DataFlow executions, generating
a CVS-like file where all the informations on how many rows have been processed by
each dataflow component will be available.
You can see an example of this file here:
I suggest to download it so that you can start to play and see how many things you
can do with all that informations.
Here is the Package used as a sample:
Of course with such file it's very very very very easy to import everything in a SSIS
Performance Database that you can create for that purpose, so that you can do query
like this one:
With that you can aggregate data and monitor how your package is performing on daily
These are the data currently avaiable in the .dtsprofile file:
DTLoggedExec is capable of handling correctly complex packages situations, if you
have a DataFlow inside a loop, it detects it and populate the "ExecutionNumber" value
accordingly, so that you can profile dataflow execution data for each iteration. Of
course it also provides a timestamp so that you can calculate how many rows are processed
per minute (o per second), creating a graph where you can see the trendline of your
package execution times agains the number of processed rows (for example).
Basically you can do now some serious performance trendline analysis of packages
to instrument the package itself. All the data you need will be extracted by DTLoggedExec
itself, right from the SSIS Engine.
I now really feel that DTLoggedExec is really what DTExec should have been right from
Of course DTLoggedExec will work also with SQL Server 2008 :-)
After a while I've realeased a new version of DTLoggedExec, the DTExec replacement:
Compiled to support 32bit and 64bit platforms (32bit and 64bit executables
are available in the zipped file)
Updated the ConsoleLogProvider to add a more detailed logging of OnError events.
all properties, along with related connection properties of the erroneous task are
logged. This is IDEAL for post-mortem debugging
Corrected a little bug that prevented the abilility to load packages from SQL Server
using SQL Authentication
Display loaded package version
Added Help File
Added Samples Packages
Added Usage Samples
More info and download on CodePlex!
Finally I've realeased a new version of my DTLoggedExec tool.
A lots of improvement from the first public version:
-Switched to a fully pluggable architecture to support 3rd party plugins
-Added "LogEvent" command line switch to choose which events should be logged
-Added support for Expression logging
-Added "Package Name" and "Container ID" values in the console log
Download it from
Today I worked again on DTLoggedExec. I've been able to add the ability to log properties with DTS Expressions associated. This allows the log to show and report properties values in additions to the already present ablity to log variables values.
I'm sure you'll find this feature really helpful if you're using DTS Expression (as you should do) to add flexibility to your SSIS Packages.
The DTLoggedExec that will contain this new feature will be the version 0.1.1.0.
I've created a dedicated page for my DTLoggedExec tool.
By the end of the month (June) i'll put online the full source code. The code and the application are licensed under
Creative Commons Attribution-NonCommercial-NoDerivs 2.5 Italy License.<!--/Creative Commons License--> <!-- <rdf:RDF xmlns="http://web.resource.org/cc/" xmlns:dc="http://purl.org/dc/elements/1.1/"
xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#"> <Work rdf:about=""> <license rdf:resource="http://creativecommons.org/licenses/by-nc-nd/2.5/it/" /> <dc:title>DTLoggedExec</dc:title> <dc:date>2006</dc:date> <dc:description>A tool for running SSIS Packages
with full logging informations</dc:description> <dc:creator><Agent><dc:title>Davide Mauri</dc:title></Agent></dc:creator> <dc:rights><Agent><dc:title>Davide Mauri</dc:title></Agent></dc:rights> <dc:source rdf:resource="http://www.davidemauri.it/dtloggedexec"
/> </Work> <License rdf:about="http://creativecommons.org/licenses/by-nc-nd/2.5/it/"><permits rdf:resource="http://web.resource.org/cc/Reproduction"/><permits rdf:resource="http://web.resource.org/cc/Distribution"/><requires rdf:resource="http://web.resource.org/cc/Notice"/><requires
rdf:resource="http://web.resource.org/cc/Attribution"/><prohibits rdf:resource="http://web.resource.org/cc/CommercialUse"/></License></rdf:RDF> -->
The "NoDerives" licensing limitation just means that if you want to contribute to the project you have to join codeplex and/or contact me so that I can add you to official authors list.
The "NonCommercial" licensing limitation just means that you cannot take the code and the executable and sell it. Of course, if you want to use it in your production eviroment, that's fine.
I've been able to work a little on my DTLoggedExec project in this last week. You can find a log execution result here:
DTLoggedExec now supports some of the original DTExec options and is able to log even variables values, as you may see from the link above. DTLoggedExec will also support a plugin architecture so that creating a custom log provider will be as easy as 1-2-3.
Currently developed log provider are:
The Null log provider will just trash every information it gets. I'm using it to benchmark the impact of other providers on package execution. SqlServerProvider will send all information to a sysdtslog902 table. Note that the table is NOT the original sysdtslog90,
since DTLoggedExec can log A LOT more information than the orginala tool. The ConsoleLogProvider will just send data to the console. This is the log provider I used to create the test_output.txt file that I put on my website.
I've also a great news. I've decided to release the code under the Creative Commons license, and I'll release the project on CodePlex (I've just requested to open a new project for that) for the end of this month.
Of course any comments and feedback is appreciated!
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:
- Ability to switch on/off logging from the execution utility, not only from inside the package (and thus only using Visual Studio)
- 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.
- 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.
- 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).
Facing the Impedance Mismatch News Feed