All my previous posts are related to the niche requirements. And here I come again to discuss one more such interesting concept.
In SSIS projects, we come across few packages that accomplish a complex business logic. Such packages surely include a chain of control flow tasks. Achieving business logic is not the last step but the first. Once the business logic is achieved in SSIS package, there comes the need to optimize its execution duration which leads us to track the execution time of individual tasks in the package. The tasks which consume more time must be observed and optimized.
Solution: SSIS Logging to the table is the best way to track the duration. The pros here is we can maintain a history of the executions and compare it with the fresh executions as well. However I recommend to make the history confine to a month or so as per the need.
When we set the SSIS Package logging to SQL Server tables, a table named "SysSSISLog" is created that holds the execution details like ID, Event, TaskName, StartTime, EndTime...
Below is a step by step process to track the duration.
01. Package Design:
Below is a sample package design that includes multiple tasks few embedded in others.
02. Package Execution:
Below is the shot of package execution.
03. Execution details stored in "SysSSISLog" table:
Query displays the execution details stored in "SysSSISLog" table.
The results include event name, task name, starttime, endtime etc., There can be multiple records depending on number of the events defined for every task.
04. Execution Duration Query:
Observe the last column in the results displaying the duration of every single task. I have set the format in HH:MI:SS.
05. Conclusion:
The objective of this post was to show how to track the duration every single task takes. We very well know that the number of records may impact the execution time. But here we are assuming a static number of records processed through the package. And also here we are not discussing how to optimize the design because it depends upon the package design and the tasks that it include (may be by reducing the components in the tasks or using more of SQL statements rather than relying on SSIS transformations or any other fix).
*We can integrate the "Custom Auditing" concept here but its again a vast area which takes more time to discuss.
I hope its useful just as the previous posts.....Happy SSIS'ing. :)
In SSIS projects, we come across few packages that accomplish a complex business logic. Such packages surely include a chain of control flow tasks. Achieving business logic is not the last step but the first. Once the business logic is achieved in SSIS package, there comes the need to optimize its execution duration which leads us to track the execution time of individual tasks in the package. The tasks which consume more time must be observed and optimized.
Solution: SSIS Logging to the table is the best way to track the duration. The pros here is we can maintain a history of the executions and compare it with the fresh executions as well. However I recommend to make the history confine to a month or so as per the need.
When we set the SSIS Package logging to SQL Server tables, a table named "SysSSISLog" is created that holds the execution details like ID, Event, TaskName, StartTime, EndTime...
Below is a step by step process to track the duration.
01. Package Design:
Below is a sample package design that includes multiple tasks few embedded in others.
02. Package Execution:
Below is the shot of package execution.
03. Execution details stored in "SysSSISLog" table:
Query displays the execution details stored in "SysSSISLog" table.
The results include event name, task name, starttime, endtime etc., There can be multiple records depending on number of the events defined for every task.
04. Execution Duration Query:
Observe the last column in the results displaying the duration of every single task. I have set the format in HH:MI:SS.
05. Conclusion:
The objective of this post was to show how to track the duration every single task takes. We very well know that the number of records may impact the execution time. But here we are assuming a static number of records processed through the package. And also here we are not discussing how to optimize the design because it depends upon the package design and the tasks that it include (may be by reducing the components in the tasks or using more of SQL statements rather than relying on SSIS transformations or any other fix).
*We can integrate the "Custom Auditing" concept here but its again a vast area which takes more time to discuss.
I hope its useful just as the previous posts.....Happy SSIS'ing. :)
