When use sp_who2 looks cannot see SSIS package running info either user running it by jobs or locally; How we can trace the SSIS package information? Can some one help me?
Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.
What information are you trying to trace? Also, what version of SQL Server? You can do some native logging within the SSIS package depending on what you want. Also, utilizing the SSISDB catalog will have further runtime metrics.
Hi JohnM, our server sometime will have heavy load, we try to figure out what case it and how is the load like CPUTime, DiskIO, LoginName, HostName etc., we know there are some SSIS package run against server but we don't know detail. Can we see any SSIS package running information through sp_who2 or SSISDB catalog?
Those metrics can be gathered via PerfMon as long as you know the times in which the packages are running. You'll have to manually correlate the times/data. As mentioned in the answer by @Davtjen, you can see run time metrics through the SSISDB catalog but I don't believe they will show you hardware metrics of the server. Sp_who2 won't show you those metrics either as that'll show you session information.
Maybe this can help, you can monitor executions via SSMS by r-click SSISDB>Reports>Integration Service Dashboard, from here you can check performance etc.
19 People are following this question.
SSIS packages error
ActiveX Script errors in SSIS package
Retry logic SSIS Package
How to read SSIS packagedata field contents? (decrypt packagedata field)
SSIS 2012 - Execute DTS 2000 Package Task