Custom Logging to SQL Server table in SSIS

Question

I have a SSIS package that has three DataFlowTasks.

1st dataflow load data to destination table1
2nd dataflow load data to destination table2
3rd dataflow load data to destination table3

I configured logging by default to SQL Server table (ssiserrorlog) on error. but this only has startdate and enddate details but I want to log the details to SQL Server custom error log table like the below.

How to do this process I am new to SSIS.

score 1 · Answer 1 · answered Mar 24 '16 at 14:53

1

You can use the Row Count component in each data flow to get the number of rows loaded.

"Duration" is just the DateDiff between Start and End Date. You could even make it a computed column in your log table, if you're not content to just calculate it at query time.

answered Mar 24 '16 at 14:53

Tab Alleman

31,483
7
36
52

how to capture starttime and endtime – StackUser Mar 29 '16 at 09:08
I don't understand, you say in your question that you already have StartTime and EndTime. But if you don't, you can get it simply doing `SELECT GETDATE()` in an ExecuteSQL Task. – Tab Alleman Mar 29 '16 at 12:57

score 1 · Answer 2 · answered Mar 28 '16 at 18:58

1

Use RowCount Transformation in each of the dataflow task before loading into the Destination. And then use this value in the SSISLogging Table

As for as duration, since you know the starttime and endtime, use the DateDiff function.

answered Mar 28 '16 at 18:58

Unna

42
2

how to capture starttime and endtime – StackUser Mar 29 '16 at 09:08

score 1 · Answer 3 · answered Apr 01 '16 at 01:09

1

In SSIS, you will have system variables - start and endtime. Use the System Variables to capture Start and End Time

answered Apr 01 '16 at 01:09

Unna

42
2

Graham Eckel · Accepted Answer · 2017-10-20T15:32:21.100

A correct solution for the RowCount and Duration has been suggested, however, there are noted datatype issues between using the system::starttime variable to transfer Package or Event starttime custom logs from SSIS to SQL.

The user will have to create a user variable (ie. user::StartTime) and likely create an expression (depending on what is being used for EndTime) in order to solve this aspect of the problem.

https://sqljunkieshare.com/2011/12/09/ssis-package-logging-custom-logging-2008-r2-and-2012/

Custom Logging to SQL Server table in SSIS

4 Answers4