未加星标

Integration Services Catalog package errors

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二03 | 时间 2019 | 作者 红领巾 ] 0人收藏点击收藏

Short post today…

My client makes extensive use of SSIS and deploys the packages to the Integration Services Catalog (ISC), and runs them via hundreds of jobs.

When one of the jobs fail, I have to go get the details.

Job History doesn’t have it.

So my process was:

Get the package path out of the job step Expand the ISC Expand SSISDB Expand the Folder (get this from the job step) Expand the Projects Node Expand the correct Project (get this from the job step) Expand Packages Right-click the relevant package(get this from the job step) Go to Reports, Standard Reports, All Executions Filter for Failed Executions Cross your fingers that you can get useful info from the result.

Now, repeat for each job step that failed.

The SSIDB database has all of this in the Tables (schema Internal) and or the Catalog Views.

It took a while, but I created this to run around those tables and views to get ALL of the errors for the last day:

/* Created by Kevin Hill, Dallas DBAs LLC, 12/28/2018 Inspired by work from Jules Behrens This queries multiple SSISDB tables to return a clear path from Top to bottom related to errors in an Integration Services Catalog based SSIS package. It has not yet been tied back to job execution, nor is it set to email info out. Use this as a backup to your normal job failure checks to tie it all together instead of spending a full cup of coffee clicking and drilling into the cumbersome All Executions report. Free to use and modify, please leave this header as a courtesy. */ Select fold.[name] as Folder_name ,proj.[name] as Project_Name ,pack.[name] as Package_Name ,mess.[message_source_name] ,mess.[message] ,mess.[message_time] --,mess.[execution_path] -- this is a pretty long path if you are pasting into an email or Excel From [catalog].[projects] proj Join [catalog].[packages] pack on proj.project_id = pack.project_id Join [catalog].[folders] fold on fold.folder_id = proj.folder_id Join [catalog].[executions] execs on execs.folder_name = fold.[name] and execs.project_name = proj.[name] and execs.package_name = pack.[name] Join [catalog].[operations] ops on execs.execution_id = ops.operation_id join [catalog].[event_messages] mess on ops.[operation_id] = mess.[operation_id] Where 1=1 and mess.message_type in (120) -- errors only --and mess.message_type in (120,130)-- errors and warnings and mess.message_time > getdate() - 1 -- adjust as necessary

If you have a very active server, this can take awhile as the indexes on the underlying tables are for the cascading delete cleanup process. I’m working on a set of good indexes that will likely be unsupported by Microsoft, but useful.

Adjust as you see fit. Please leave the header if you use it.

Thanks for reading!

Kevin3NF

Follow @Dallas_DBAs

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

代码区博客精选文章
分页:12
转载请注明
本文标题:Integration Services Catalog package errors
本站链接:https://www.codesec.net/view/627933.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(54)