Davide Mauri Blog

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 in September 2008 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:

http://www.davidemauri.it/files/Test-Package4.dtsprofile.txt

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: http://www.davidemauri.it/files/Test-Package4.zip

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 basis.

These are the data currently avaiable in the .dtsprofile file:

DataFlowId, 
DataFlowName, 
ExecutionNumber,
TransformationId, 
TransformationName,
SourceId, 
SourceName,
DestinationId, 
DestinationName,
RowCount

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 without having 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 the start.

Of course DTLoggedExec will work also with SQL Server 2008 :-)