Vous êtes ici :
Quelques requêtes pour interroger le catalogue SSIS
Transact-SQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
/*------------------------------------------------------------------- [SCRIPT] Requêter le catalogue SSIS [DESCRIPTION] Trouver les messages d'erreur [MAJ PAR] DATAFLY - Arian Papillon [DATEMAJ] 20200125 -------------------------------------------------------------------*/ SELECT session_id, request_id, physical_operator_name, node_id, thread_id, row_count, estimate_row_count FROM sys.dm_exec_query_profiles ORDER BY node_id DESC, thread_id; SELECT O.object_name AS FailingPackageName , O.object_id , O.caller_name , O.server_name , O.operation_id , OM.message_time , EM.message_desc , D.message_source_desc , OM.message FROM catalog.operation_messages AS OM INNER JOIN SSISDB.catalog.operations AS O ON O.operation_id = OM.operation_id INNER JOIN ( VALUES (-1,'Unknown') , (120,'Error') , (110,'Warning') , (70,'Information') , (10,'Pre-validate') , (20,'Post-validate') , (30,'Pre-execute') , (40,'Post-execute') , (60,'Progress') , (50,'StatusChange') , (100,'QueryCancel') , (130,'TaskFailed') , (90,'Diagnostic') , (200,'Custom') , (140,'DiagnosticEx Whenever an Execute Package task executes a child package, it logs this event. The event message consists of the parameter values passed to child packages. The value of the message column for DiagnosticEx is XML text.') , (400,'NonDiagnostic') , (80,'VariableValueChanged') ) EM (message_type, message_desc) ON EM.message_type = OM.message_type INNER JOIN ( VALUES (10,'Entry APIs, such as T-SQL and CLR Stored procedures') , (20,'External process used to run package (ISServerExec.exe)') , (30,'Package-level objects') , (40,'Control Flow tasks') , (50,'Control Flow containers') , (60,'Data Flow task') ) D (message_source_type, message_source_desc) ON D.message_source_type = OM.message_source_type WHERE OM.message_type = 120 ORDER BY OM.message_time DESC |
Transact-SQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 |
/*------------------------------------------------------------------- [SCRIPT] Requêter le catalogue SSIS [DESCRIPTION] Messages d'erreur pour la dernière exécution [MAJ PAR] DATAFLY - Arian Papillon [DATEMAJ] 20200125 -------------------------------------------------------------------*/ SELECT O.object_name AS FailingPackageName , O.object_id , O.caller_name , O.server_name , O.operation_id , OM.message_time , EM.message_desc , D.message_source_desc , OM.message FROM SSISDB.catalog.operation_messages AS OM INNER JOIN SSISDB.catalog.operations AS O ON O.operation_id = OM.operation_id INNER JOIN ( VALUES (-1,'Unknown') , (120,'Error') , (110,'Warning') , (70,'Information') , (10,'Pre-validate') , (20,'Post-validate') , (30,'Pre-execute') , (40,'Post-execute') , (60,'Progress') , (50,'StatusChange') , (100,'QueryCancel') , (130,'TaskFailed') , (90,'Diagnostic') , (200,'Custom') , (140,'DiagnosticEx Whenever an Execute Package task executes a child package, it logs this event. The event message consists of the parameter values passed to child packages. The value of the message column for DiagnosticEx is XML text.') , (400,'NonDiagnostic') , (80,'VariableValueChanged') ) EM (message_type, message_desc) ON EM.message_type = OM.message_type INNER JOIN ( VALUES (10,'Entry APIs, such as T-SQL and CLR Stored procedures') , (20,'External process used to run package (ISServerExec.exe)') , (30,'Package-level objects') , (40,'Control Flow tasks') , (50,'Control Flow containers') , (60,'Data Flow task') ) D (message_source_type, message_source_desc) ON D.message_source_type = OM.message_source_type WHERE OM.operation_id = ( SELECT MAX(OM.operation_id) FROM SSISDB.catalog.operation_messages AS OM WHERE OM.message_type = 120 ) AND OM.message_type IN (120, 130); |
Table of Contents