How to log each container and task details into a table in SSIS

Question

I have a package which contains several containers and each container have multiple tasks in it like below.

enter image description here

during package execution, i need to log each container and task details into a table like below.

LogID  Container        Task                        Status   Error                                       LoggedOn
1      SEQ - Customer   truncate customer table     SUCCESS                                              2015-03-31 02:22:50.267
2      SEQ - Customer   create temp table to store  SUCCESS                                              2015-03-31 02:22:50.267
3      SEQ - Customer   DF - Loading Customers      SUCCESS                                              2015-03-31 02:22:50.267
4      SEQ - Customer   Validating Customers        FAILED   Failed to convert from varchar to bigint    2015-03-31 02:22:50.267

If any error occurs at any particular task, it should log error description in error column in table.Please help me how to achieve this through event handlers or logging in ssis.

That is a broad topic. What have you tried and where are you stuck? — Tab Alleman, Mar 31 '15 at 14:47
I am thinking of to do with OnPostExecute event handlers and in this i can log only package and task details but not container details. Do you have any idea how to achieve this. table should have all the info like package,container,task — Ram Das, Apr 01 '15 at 08:35
Every SSIS object has event handlers. If you need to get down to the Task level, put an event handler in the OnError event of each task. For the ones that don't error, you can use the PostExecute handler of each task. — Tab Alleman, Apr 01 '15 at 13:33

score 1 · Answer 1 · answered Apr 02 '15 at 06:03

I got this by using OnPreExecute,OnError Event handlers.i have selected this to event handlers on package level and have created one variable :: Container(string).Select the package and generate these two event handlers.

Used following script in Execute SQL Task-->OnPreExecute Event.

SourceDescription-- Input
PackageName-- Input
Container-- Input
SourceName-- Input
Container-- Output

DECLARE @TaskType VARCHAR(500),@Package VARCHAR(500),@Container VARCHAR(500),@Task varchar(500)
SELECT @TaskType = ?,@Package =?,@Container= ?,@Task = ?

IF(@TaskType ='Sequence Container')
BEGIN
    SET @Container = @Task
END
ELSE IF(@Package <>@Task)
BEGIN
    INSERT INTO LogTable(Package,Container,Task,Status)
    SELECT @Package,@Container,@Task,'SUCCESS'
END

SET ? = @Container

and below script in OnError Event

SourceDescription-- Input
PackageName-- Input
Container-- Input
SourceName-- Input
ErrorDescription--Input

DECLARE @TaskType VARCHAR(500),@Package VARCHAR(500),@Container VARCHAR(500),@Task varchar(500),@Error VARCHAR(1000)
SELECT @TaskType = ?,@Package =?,@Container= ?,@Task = ?,@Error=?

IF(@TaskType ='Sequence Container')
BEGIN
    SET @Container = @Task
END
ELSE IF(@Package <>@Task)
BEGIN
    INSERT INTO LogTable(Package,Container,Task,Status,Error_Desc)
    SELECT @Package,@Container,@Task,'FAILED',@Error
END

How to log each container and task details into a table in SSIS

1 Answers1