0

Using SQL Server Management Studio, I am trying to gauge how many Bytes of data are being processed by SSIS packages. I know how to generate reports in Integration Services Catalogs to view statistics and messages, but these seem to only give statistics on time duration. The following is a sample of the Execution Performance statistics that are provided. Is there a way to view the number of Bytes that were read/written for each package?

ID:05050505      Start Time: 1/5/2023 1:24:06 AM  Duration(sec): 2051.417

At present I don't have direct access to the server logs.

Dale K
  • 25,246
  • 15
  • 42
  • 71
user8675309
  • 49
  • 6
  • Off the top of my head, there's not going to be an easy, generic solution. You could write a thing that cracks open a package and looks at the data flow but then what, is the it the number of bytes pulled from the source? The number of bytes sent to a destination? But what if we needed to use a Lookup to identify whether data _should_ flow to the destination, do those bytes count? What about if there's a LOB (DT_TEXT, DT_NTEXT, the other one) field in the data flow, each field on each row would need to be captured for this metric. Perhaps I misunderstand the root problem you're trying to solve – billinkc Jan 05 '23 at 16:10
  • Thanks for your input. Several of our scheduled jobs started taking significantly longer to complete since 12/30. Im trying to determine if there has been a larger volume of Data than normal. Normally I would look at the source files, but this warehouse pulls data from Oracle Databases that I only have read access to. As of now I'm trying to compare the duration time of various packages within the master pkg to see if there are any that took way longer than normal. – user8675309 Jan 05 '23 at 16:30
  • Ah, in that case you can try changing the Logging level from the default of Basic to ... I think it's Performance but might be Verbose. One of those two levels will result in metrics being written to the (name approximate) catalog.component_summary_statistics which will give you a breakdown of how long time was spent at each component in a package. Doesn't do you much good as you won't have a pre 2023 set to compare to but you can at least see "Is it the Oracle Source" that's laggardly or destination or some other component – billinkc Jan 05 '23 at 16:53
  • 1
    Tim Mitchell has a post that can get you started on the logging levels. He demonstrates the custom logging levels which are new in 2016 but you can leapfrog from there into more articles https://www.timmitchell.net/post/2016/12/22/ssis-custom-logging-levels/ – billinkc Jan 05 '23 at 16:53

0 Answers0