dinsdag 19 oktober 2010

SSIS Framework (Part I)

In one of my projects i'm building a SSIS framework as base for housing of all sorts of different SSIS packages. The purpose is to get all SSIS packages standardized in order to get advantages like uniformity, monitoring, one central location for storing meta information, quick error solving, performance tuning, etc.

We have established a framework with all kind of features:
  • Automatic logging of package information in a SQL Server services database.
  • Automatic logging of package information in a file.
  • The use of checkpoints in a package.
  • The logging of errors in a a SQL Server services database.
  • Putting bufferfiles in the projectfolder and not in the TEMP location.
  • Counting the following measures: initial rowcount, final rowcount, extract count, insert count, extrat erorr count and insert error count.
Mainly, in the diagram below is the overall SSIS package shown.There are 5 steps: Truncate of the destination table, Start of audit proces, the dataflow task, ending of the audit proces and deleting of the empty error files and old archive files.

Next time i'll show the steps in more detail.


2 opmerkingen:

  1. Why not use a stored procedure in pre en postlogging for auditing purposes. When you want to change someone in the logic, just change the procedure. When using this technique and something need to be changed or a bug is found, all packages need to be changed. This can be a big job in large environments.

  2. Hi Ronald,

    Thanx for your comments. I'm not sure what you mean. There are two auditing steps (SEQ_BeginAudit and SEQ_EndAudit). The first one gathers information and writes in a Packageaudit table and objecttable (can be table). After the DFT has run more information is collected and written in hte Package audit and object audit table.

    Hope to make things clearer...