The ideal strategy depends on the below set of factors:
- What are the metrics that you are trying to capture? Are those like Pipelinerunid, starttime,endtime,datarows,datasize etc? Or are those at each and every activity level metrics?
Because in case if those are the initial ones, you can have a stored procedure activity within the main pipeline itself and pass the pipeline parameters/expression to capture those metrics directly.
- How long do you want to retain the data? Because diagnostic setting/log analytics based on what I remember has a fixed retention period post which it cleans up historical records, so in that case having a custom SQL table of your own helps you govern the retention time period.