Logging in SSIS
[296 byte] By [bond12] at [2007-12-24] 
bond12 wrote:
Logging in SSIS as compared to DTS is more complex to set up with so many events. What events should I choose if I need something similar to DTS package logging i.e. I simply want to see which tasks executed at what time and whether they failed or succeded and if they failed, what was the error?
Good question. At a minimum you should log:
- OnError
- OnInformation
- OnPostExecute
- OnWarning
That's just my opinion of course.
-Jamie
JamieThomson at 2007-8-31 >
In the SSIS few projects I have worked so far, I have chosen only onError and onWarning events; and even with these two I got many rows caused by the same error/warning, which is something i dot really like. For loging the start/end times I use custom tables that also captures other execution metadata.
I never have used DTS, so sorry I cannot offer a comparative point of view
Rafael Salas
RafaelSalas at 2007-8-31 >
I use custom tables as my primary logging mechanism and I use the builtin logging as a secondary mechanism. I use Reporting Services to generate reports from my custom tables. I'm also using the free reports from MS to look at the builtin logging tables.
Thanks,
Greg Van Mullem
GregVanMullem at 2007-8-31 >
Here is a link to the free Reporting Services report pack for SSIS events. I have also found them to be helpful, although the reports could use a little TLC on their appearance.
FrankJDeFalco at 2007-8-31 >
Hi,
I would suggest you to check only "OnError" and "OnTaskFailed" , these give you the information you are looking for, typically it would be similar to what it was in DTS.
Thanks
db_guy at 2007-8-31 >
I'm currently logging ALL builtin events. I'm only keeping the last 5 days worth of event records. I have a log cleainging job that run every night deleting old records. It's only about 120,000 records and minimal disk space. I have plenty of disk space so I might as well use a little of it!
Someday as I add more SSIS packages I will need to go with a scheme like the one Jamie suggested. Note: I keep my custom event log records for 60 days.
Later,
Greg Van Mullem
GregVanMullem at 2007-8-31 >
I totally agree, I'm logging all of them too in my customized class.
enricvives at 2007-8-31 >
Mabye it can be helpful from you. This a real schema (ordered) for one of our DTSX:
Hacer Getdate() is the name of Sql Task and Segunda Tarea too.
Acción | Evento | TaskHost | Package | Qu hace | Valor |
Validate | OnPreValidate |
| x | Revisa la configuración general del paquete por si tiene algo mal antes de ejecutar | p.Name = "{352DABF6-C5EF-4AD9-9810-F1637716F679}" |
| OnPostValidate |
| x | p.Name = "{352DABF6-C5EF-4AD9-9810-F1637716F679}" | |
LoadFromSqlServer | OnError |
| x | Lo carga en memoria. Si hay algo mal se disparan los eventos. |
|
| OnWarning |
| x |
| |
Execute | OnPreValidate |
| x | Lo Ejecuta | p.name = "NombrePaquete" |
| OnPreValidate | x |
|
| t.name = "TaskHost" |
| OnInformation | x |
|
|
|
| OnProgress | x |
|
| Validando Data Flow (0%) |
| OnProgress | x |
|
| Validando Data Flow (50%) |
| OnProgress | x |
|
| Validando Data Flow (100%) |
| OnPostValidate | x |
|
|
|
| OnPreValidate | x |
| TAREA SQL | Hacer Getdate() |
| OnPostValidate | x |
| TAREA SQL | Hacer Getdate() |
| OnPreValidate | x |
| TAREA SQL | Segunda Tarea |
| OnPostValidate | x |
| TAREA SQL | Segunda Tarea |
| OnPostValidate |
| x |
| p.name = "NombrePaquete" |
| OnQueryCancel |
|
|
| Return false / true |
| OnPreExecute |
| x |
| p.name = "NombrePaquete" |
| OnQueryCancel |
|
|
| Return false / true |
| OnPreExecute | x |
| TAREA SQL | Hacer Getdate() |
| OnPreValidate | x |
| TAREA SQL | Hacer Getdate() |
| OnPostValidate | x |
| TAREA SQL | Hacer Getdate() |
| OnQueryCancel |
|
|
| Return false / true |
| OnProgress | x |
| TAREA SQL | Hacer Getdate() |
| OnQueryCancel |
|
|
| Return false / true |
| OnPostExecute | x |
| TAREA SQL | Hacer Getdate() |
| OnQueryCancel |
|
|
| Return false / true |
| OnPreExecute | x |
| TAREA SQL | Segunda Tarea |
| OnPreValidate | x |
| TAREA SQL | Segunda Tarea |
| OnPostValidate | x |
| TAREA SQL | Segunda Tarea |
| OnQueryCancel |
|
|
| Return false / true |
| OnProgress | x |
| TAREA SQL | Segunda Tarea |
| OnQueryCancel |
|
|
| Return false / true |
| OnPostExecute | x |
| TAREA SQL | Segunda Tarea |
| OnQueryCancel |
|
|
| Return false / true |
| OnPreExecute | x |
| DATA FLOW | t.name = "Data Flow Task" |
| OnPreValidate | x |
| DATA FLOW | t.name = "Data Flow Task" |
| OnInformation | x |
| DATA FLOW | Iniciando fase de validación |
| OnProgress | x |
| DATA FLOW | Validando Data Flow (0%) |
| OnProgress | x |
|
| Validando Data Flow (50%) |
| OnProgress | x |
|
| Validando Data Flow (100%) |
| OnPostValidate | x |
|
|
|
| OnInformation | x |
|
|
|
| OnProgress | x |
|
| Fase de ejecución (0%) |
| OnProgress | x |
|
| Fase de ejecución (50%) |
| OnProgress | x |
|
| Fase de ejecución (100%) |
| OnInformation | x |
|
|
|
| OnProgress | x |
|
| Ejecutar previamente (0%) |
| OnInformation | x |
|
| Se inicia el procesamiento del archivo |
| OnProgress | x |
|
| Ejecutar previamente (50%) |
| OnProgress | x |
|
| Ejecutar previamente (100%) |
| OnInformation | x |
| DATA FLOW | INICIO FASE DE EJECUCIóN |
| OnInformation | x |
| DATA FLOW | El no total de filas es n |
| OnInformation | x |
|
|