Welcome
Guest
, you are in:
<root>
•
Login
DTLoggedExec - Instrumenting SSIS Packages
Navigation
¶
Main Page
Index Page
Random Page
Create a new Page
All Pages
Categories
Navigation Paths
Administration
File Management
Create Account
Search the wiki
»
Back
DTLoggedExec
Modified on 2010/06/16 10:28
by
dmauri
Categorized as
Generic
==What is DTLoggedExec?== DTLoggedExec is a tool that allows you to run a Sql Server Integration Services (SSIS) Package producing a full and detailed logging informations of execution status and package runtime data, including dataflow profiling information. '''In brief it allows to fully log and instrument package execution.''' Here's some samples of what you can do with all the data that DTLoggedExec is able to produce at runtime for '''any''' package, new or existing: [image|Package Logged Data|{UP}Using%20the%20Console%20Log%20Provider%2fTest-Package-1-Log.png|{UP}Using%20the%20Console%20Log%20Provider%2fTest%20Package%201.output.txt] [image|DataFlow Perf. Over Time|{UP}DataFlow%20profiling%2fPackage-DataFlow-Performance-Over-Time-Small.png|{UP}DataFlow%20profiling%2fPackage-DataFlow-Performance-Over-Time.png] [image|DataFlow Perf. Overall Rows|{UP}DataFlow%20profiling%2fDataFlow-Performance-Overall-Processed-Rows-Small.png|{UP}DataFlow%20profiling%2fDataFlow-Performance-Overall-Processed-Rows.png] [image|Quality Of Service|{UP}DataFlow%20profiling%2fQuality-Of-Service-Small.png|{UP}DataFlow%20profiling%2fQuality-Of-Service.png] Current released version: '''1.0.0.2'''{BR} Download from codeplex: [http://www.codeplex.com/DTLoggedExec/Release/ProjectReleases.aspx] If you want to start to use it immediatly and don't want to read the following sections, just go to the [Index|Index Page]. ==About this documentation== I'm in the process of updating this website content so that it will reflect all the changes and feature inclued in version 1.0.0.1. Please be patient while I'm doing this....in my free time :-) So it may take a while. ==Latest News== 2010-06-11: '''DTLoggedExec 1.0.0.2 STABLE has been released'''. Fix bug found in /SQL and /DTS parameters and added support for {filename} token 2010-05-08: '''DTLoggedExec 1.0.0.1 STABLE has been released'''. This release is a STABLE version and not a Beta anymore! 2009-11-28: '''DTLoggedExec 0.2.3.0 has been released'''. This release also include reports to analyze gathered log data! 2009-10-07: Website upgraded to version 3.0.333 of ScrewTurn Wiki. Should be way more stable now :). 2009-09-06: Updated the "Log Providers" documentation. 2009-07-22: Added documentation for [Package Parameters]. 2009-06-29: '''DTLoggedExec 0.2.2.0 has been released''', with a lot new features! ==Why you created DTLoggedExec?== If you're using Sql Server 2005/2008 Integration Services, you should know that the packages you've created can be launched via DTExec or SQL Server Agent. In both cases you'll find that, though an extensive logging infrastucture seems to be present, logged information doesn't really help you to understand why and how a package that has always run correctly someday decides to go wrong. In addition to this big limitation you may also have already discovered that logging can't just be switch on or off right from the execution tool. Though it seems possibile, if you want to log on a text file your package MUST contain a connection manager that points to the file you want, otherwise logging cannot be used. This means that you should engineer your packages so that they support the logging infrastructure, BEFORE you need it. This also means that you have to take care of preparing the way for logging in ALL of your packages. This is really not how logging should work for me. I need something that can be switched on or off as I desire, without having me to do anything during package development; in addition I need ALL the data and the information I can have, since when I have to understand why a package didn't run succesfully in a production enviroment, well, I can't run the package in debug mode from Visual Studio right from the production server (otherwise I'll be doing debugging on production machines, not an healthy way of working from my point of view)! Another missing feature that I really missed a lot is the ability to profile dataflow tasks. To do a proper <a style="text-decoration:none; color:#555555;"href="http://www.superiorpapers.com/">paper writing</a> profiling you should add "Row Count" wherever you need to know how many rows has been processed, take the start and the end time of each transformation and put everything in a database for later analysis. This is a highly time consuming way to work IMHO, since package instrumentation should be something provided right from the execution tool, and not by who creates the pakages. Also, putting the instrumentation logic within the package make it more complex and long to develop. So I decided to leverage all the power hidden in the Integration Services Object Model (which is really cool) and I created the DTLoggedExec tool, which aims to be a DTExec replacement, putting into it all the thing I need to work at my best. ===And who are you?=== My name is '''Davide Mauri''' and I'm an IT consultant, specialized on the Microsoft Data Platform, in particular on SQL Server and Business Intelligence. I'm an MCP, MCAD, MCDBA, MCT, [https://mvp.support.microsoft.com/default.aspx/profile=D2CB2B02-9716-4191-8C63-A5AA5FA56B48|MVP on SQL Server], and I've been working with SQL Server since version 6.5. My interests cover the whole platform, from the Relational Engine to Analysis Services, from architecture definition to performance tuning. I have a very strong developer background, and I know very well Object-Oriented Design & Programming, and I also have a deep knowledge of the .NET framework. You can find more on me on [http://www.davidemauri.it|my website]. I actually work as a Mentor for [http://http://www.solidq.com/na/Default.aspx|Solid Quality Mentors] and you can find me speaking in many Italian and internationals events. I'm also the leader of the first and biggest Italian SQL Server User Group, [http://www.ugiss.org|UGISS]. You can find me virtually on the forums, on my blog and - of course - in flesh in any UGISS event. [image||{UP}MainPage%2fMVP_Logo.png] [image||{UP}MainPage%2fSQM_Logo.gif] ==Features== DTLoggedExec has a lot of interesing functionalities: *'''Enable / Disable logging on the fly for any package, just right from the command line''' *'''Log Variables value''' *'''Log Values of Properties bound to Expressions''' *In case of error, log all properties along with related connection properties of the erroneous task. This is IDEAL for post-mortem debugging *Fully pluggable architecture: decide where to log informations (natively supported: No Log, Console, CSV) Add your own log providers to customize logging *Choose which event you want to log *DTExec parameters emulation (not yet 100% finished) *Compiled to support 32bit and 64bit platforms (32bit and 64bit executables are available in the zipped file) *Support for Integration Services 2005 or 2008 through assembly binding *'''DataFlow Profiling''': log DataFlow performance data, how many rows each component has processed in how much time *'''Log to CSV''' *'''Import Log and Profiling Data into SQL Server for further analysis automatically''' *Add parameters support to packages ==Supported Platforms and Architectures== DTLoggedExec supports all available platforms and architectures. Platforms: SQL Server 2005 and 2008 Architectures: 32bit, x64, IA64 It comes with two precompiled executables. One that will always be executed in 32bit, ideal for all the packages that needs to use the old oledb provider drivers such as Jet Engine, and another executable compiled to run on any architecture, configuring itself automatically to run in 32bit or 64bit mode. ==Online Manual== Go to the [Index] page.
ScrewTurn Wiki
version 3.0.0.333. Some of the icons created by
FamFamFam
.