Agile SSIS series (5/12): What is the smartest way to log SSIS?

Sprint 1, which was described in the previous text, the focus was on understanding how data centric applications can be developed using agile approach. Besides that possible solutions to the logging issues have been reviewed. In sprint 2, all this knowledge will be incorporated into the architecture of the solution. Two diagrams will be produced as the result of work in sprint 2. “Data flow diagram capturing architecture of the solution” shows the architecture of the solution using data flow diagram. The second chart “Entity-relationship diagram (logical)” captures the proposed data structures in which the logging data will be stored. Please note that only the first diagram will be part of this text, second will come later on.

Data flow diagram capturing architecture of the solution

As mentioned above, the logging will capture data into a database. However, the SSIS packages will not be writing the data to the database directly using INSERT statements, but will use stored procedure to do that. The stored procedure provides abstraction and also supports code reuse as the logic will be part of the procedure rather part of every single SSIS package. Once the data is saved into the database, users will be able to report on it. To make the logging as robust as possible, fail over capability will be added and this is why the packages will still log into the log file as well. Connection to a database can time out or be interrupted as it is likely that the SSIS packages will be executing on a different server than the logging database will reside. So having a log file on the very same server where the DTSX package runs is beneficial as it will work as a log of a last instance in case of some significant failure. However, while logging into the database will be very low level, descriptive and detailed, only errors, warnings and custom events will be written into the log file. Finally, one has to address the issue of the daily maintenance. The problem here is that logging is likely to produce substantial amounts of data and it is required to have some maintenance processes in place to delete off old data. In case of the database, stored procedure will be deleting off old data and for the log file, PowerShell script will be used to trim older data.

Data flow diagram

Few last words...

In this text I have described the data flow diagram of FLOGS which was designed in the sprint 2. In the next article I will carry on describing the work which was done also as part of sprint 2 – the entity-relationship diagram so stay tuned!

Previous articles in the series

• Agile SSIS series (1/12): High performance SSIS logging platform
• Agile SSIS series (2/12): Top 4 features that SSIS logging platform must have
• Agile SSIS series (3/12): Why do we REALLY use agile approach?
• Agile SSIS series (4/12): Different ways how to do SSIS logging

Want to know more about Agile BI? Please read my agile BI series...