I have seen this question
SSIS 2012 – How to Query Currently Running Packages in T-SQL?
It gives me the following script:
SELECT
E.execution_id
, E.folder_name
, E.project_name
, E.package_name
, E.reference_id
, E.reference_type
, E.environment_folder_name
, E.environment_name
, E.project_lsn
, E.executed_as_sid
, E.executed_as_name
, E.use32bitruntime
, E.operation_type
, E.created_time
, E.object_type
, E.object_id
, E.status
, E.start_time
, E.end_time
, E.caller_sid
, E.caller_name
, E.process_id
, E.stopped_by_sid
, E.stopped_by_name
, E.dump_id
, E.server_name
, E.machine_name
, E.total_physical_memory_kb
, E.available_physical_memory_kb
, E.total_page_file_kb
, E.available_page_file_kb
, E.cpu_count
, F.folder_id
, F.name
, F.description
, F.created_by_sid
, F.created_by_name
, F.created_time
, P.project_id
, P.folder_id
, P.name
, P.description
, P.project_format_version
, P.deployed_by_sid
, P.deployed_by_name
, P.last_deployed_time
, P.created_time
, P.object_version_lsn
, P.validation_status
, P.last_validation_time
, PKG.package_id
, PKG.name
, PKG.package_guid
, PKG.description
, PKG.package_format_version
, PKG.version_major
, PKG.version_minor
, PKG.version_build
, PKG.version_comments
, PKG.version_guid
, PKG.project_id
, PKG.entry_point
, PKG.validation_status
, PKG.last_validation_time
FROM
SSISDB.catalog.executions AS E
INNER JOIN
ssisdb.catalog.folders AS F ON F.name = E.folder_name
INNER JOIN
SSISDB.catalog.projects AS P ON P.folder_id = F.folder_id
AND P.name = E.project_name
INNER JOIN
SSISDB.catalog.packages AS PKG ON PKG.project_id = P.project_id
AND PKG.name = E.package_name;
But it does not answer my quest. I am investigating the reasons why packages fail and I need to get hold of the error messages.
Where can I find it?
I would like to use T-SQL to query for the error message.
I have also this script below, that takes me near, but not quite:
SELECT
q.*
FROM
(SELECT em.*
FROM SSISDB.catalog.event_messages em
WHERE em.operation_id = (SELECT MAX(execution_id)
FROM SSISDB.catalog.executions)
AND event_name NOT LIKE '%Validate%') q
/* Put in whatever WHERE predicates you might like*/
--WHERE event_name = 'OnError'
WHERE package_name = 'InfoGroup Feed.dtsx'
--WHERE execution_path LIKE '%<some executable>%'
ORDER BY message_time DESC
This is the email I would like to tackle, how did they get to that error message:
Any information as how to troubleshoot SSIS errors is welcome.
Best Answer
I have a handful of queries I use. The general concepts are that the table with the information is
catalog.operation_messages
and you are interested in events with a 120 (error) type.Depending on how robust of a query you want to build out, the following two derived tables might also be of interest.
I use queries like this to find information about the errors. Maybe I only care about what the errors were (query 1). Other times, I want to know all the activities of all the operations that failed (query 2). Generally, I'm lazy and want to see all the information about the last failing operation (query 3 and note the caveat).
Perhaps I'm lazy and don't want to go look this information up in the event of a failure, much as your team appears to have done. I have a SQL Agent job that runs on demand and I have jobs that run SSIS packages set to run that job in the event of failure.
Adjust as you like