Detailed information about current or recent executions can sometimes be difficult to find. Luckily, all the detailed information is logged in real time in the project repository and available for analysis. The SQL script attached at the bottom of this article helps solve this by querying tables from the repository returning detailed execution information.
To use this script:
- Download the .sql script attached below and open in SQL Server Management Studio.
- Adjust the USE clause to match your TX Repository Database name.
- Execute the Query to retrieve the results.
- [Optional] un-comment the WHERE clause to filter on project or package name.
Here is an example result from the query with some columns filtered out (right click > open in new window to see it better):
Granularity of the Result Set
Be aware that this query pulls stats about both the (parent) Execution Package AND the individual (child) steps contained in the package. So the (parent) Execution Package information will be repeated multiple times when it contains multiple (child) steps.
Row Counts & Throughput
When "Log Row Counts" is enabled in your execution package, this script also returns [Raw Rows Processed] and [Rows Per Minute] allowing you to review data processing speed.
Note: Be careful when aggregating these fields as [Raw Rows Processed] shows the same number of rows for each step. This could potentially exaggerate your results if your not careful when aggregating.
Further Analysis and Use Cases
You may also consider embedding this query into a Database View, allowing you to more easily query or aggregate this data (just be sure to remove the ORDER BY clause at the end).
Finally, you could also embed this query as a PowerBI data source to create powerful visualizations on this data (remove the ORDER BY clause at the end).