Custom SSIS Logging...

In one of my blogposts I had blogged about using the Logging Options Provided by SSIS. The options available in SSIS are good. Depending on one's needs one could develop Custom SSIS logging solution. One of the options I have utilised is to use a table in a SQL Server database, create the table with meaningful columns which capture information that would help in troubleshooting and auditing. The columns which i would like to include are source_rows, Inserted_rows,error_rows. These would enable a developer to keep track of how data is flowing through a SSIS package. The audit table for example could be named as SSIS_Package_Audit with columns such as ( UniqueId(Identity),PkgName, StartDate, EndDate, SourceRowCnt, InsertRowsCnt, ErrorRowCnt,ElapsedTime_m, LastError,ServerName,DatabaseName). When a Package starts as one of the first steps in the SSIS package, a new row is inserted into the table to indicate that the package has started. As the package progresses through different points, the audit table can be updated. In case the package encounters an error, an update can be made to the LastError Column in the table with an appropriate error text. This could tell us what error was encountered when the package was run. Inserts and Updates to the audit table can be done through a Execute SQL Task or a Script Task.

I have included a image for reference, The first script task inserts a row, the last script task actually updates the row which was inserted with values for the rowcounts and enddate, Last error column would be set to NULL. This would indicate a good run of the SSIS package. The solution being presented here is a starting point, this can be greatly enhanced to suit the business needs and dependencies of packages could be included in the table.