How do I capture the start time of a package and how long in munutes it took to complete?

Question

How do I capture the start time of a package and how long in munutes it took to complete?

paulze2000

SSC-Addicted

Points: 412
More actions
February 10, 2015 at 8:57 am

#398003

I created an ssis project which has a Master package. This package runs a series of execute package tasks. I would like to be able to capture in a sql database table The name of the task, the start time each package task began to execute, and the amount of time in minutes it took for the package task to run.
What I have done so far is to follow each package task with a SqlTask to insert the data. But I am having a hard time configuring the variables correctly to accomplish the goal. System::StartTime does not seem to work because it captures the start time of the Master Package. Perhaps I need to capture the inside each package and insert to the table instead of trying to do it all in the master.
Any Ideas?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63519 More actions · Answer 1

use the ExecuteSQL task to run a stored procedure that accepts a string where you pass what part of the package/packagename and write it to a "log" table in your SQL database.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

I haven't tried it, but you might be able to use Event Handlers to insert into "log" tables.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

happycat59 One Orange Chip Points: 29389 More actions · Answer 3

Why not just use the out of the box package logging ? It is real easy to get it to write to a database table. You can configure it to log start/end of packages and tasks (and a heap of other events as well). You do not need to write any code to write the logs.

SQL.AP Hall of Fame Points: 3244 More actions · Answer 4

you can schedule your package with the SQL job and then check the all information from (msdb..dbo.sysjobhistory) table.

OR

you can use the system variable ("StartTime") and end time with the help of getdate() function.

____________________________________________________________

AP