ETL Performance Auditing - Part 2: Auditing Data Staging Phase

  • Comments posted to this topic are about the item ETL Performance Auditing - Part 2: Auditing Data Staging Phase

    Frank Banin
    BI and Advanced Analytics Professional.

  • Good article, thanks for sharing the idea, however I have a complex ETL where I am thinking of implementing this Audit but the problem is, Manually creating the Package/TaskID, Name pair and its child relationship in the table is not practical, since I have around 9 dataflow tasks with approx around 700+ tasks overall.

    Secondly, the audit log table structure and where the insertion of log is happening can be explained little elaborate for the readers to understand the flow.

    Please help on this, thanks:-P.

    Regards

    Basheer

  • As to collecting Package/TaskID, Name pair info. I am working on something that everyone can use, if not I will have to address individual needs.

    Below is the audit log table structure

    CREATE TABLE [sysssislog]

    (

    [id] [INT] NOT NULL IDENTITY PRIMARY KEY,

    [event] [SYSNAME] NOT NULL,

    [computer] [NVARCHAR] (128) NOT NULL,

    [operator] [NVARCHAR] (128) NOT NULL,

    [source] [NVARCHAR] (1024) NOT NULL,

    [sourceid] [UNIQUEIDENTIFIER] NOT NULL,

    [executionid] [UNIQUEIDENTIFIER] NOT NULL,

    [starttime] [DATETIME] NOT NULL,

    [endtime] [DATETIME] NOT NULL,

    [datacode] [INT] NOT NULL,

    [databytes] [IMAGE] NULL,

    [message] [NVARCHAR] (2048) NOT NULL,

    )

    ON [PRIMARY]

    after that you can for instance check out Stan Kulp's article on this website "An Introduction to Integration Services log providers" for how to enable logging into the table.

    Frank Banin
    BI and Advanced Analytics Professional.

  • Thank you for the article! This is a great and efficient way to capture information about the package/task duration, however in many cases one is also interested in some additional data, most basic one being the number of records processed or loaded. I think this would be a great enhancement to the system.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply