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 and SQL Server Jobs
SSIS Package is failing
SSIS Package Source Parameters
notes or comments in ssis package
Passing multiple records to SP in SSIS pacakge