ETL Performance Auditing - Part 2: Auditing Data Staging Phase

Question

ETL Performance Auditing - Part 2: Auditing Data Staging Phase

Frank A. Banin

SSCommitted

Points: 1631
More actions
June 11, 2012 at 11:44 pm

#392491

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.

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

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

abash2002 SSC Rookie Points: 37 More actions · Answer 1

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

Frank A. Banin SSCommitted Points: 1631 More actions · Answer 2

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.

Misha_SQL SSCertifiable Points: 5407 More actions · Answer 3

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.