How to get the start time and end time of process ssis

Question

I have an ssis package , where I includes a foreach container to loop through files and loads it in to a table. Now I want to track the process start time , end time and the status into another table. How can I achieve this.. ?

Do you need to track the whole package or the loop execution time? — Eric Hauenstein, Oct 22 '14 at 12:22
loop starting time of each file loading and time when it completes loading — Hazeena A M, Oct 23 '14 at 04:06
Please do not post duplicates. Finish your original question first. http://stackoverflow.com/questions/26479439/load-files-into-a-table-using-loop-in-ssis-2008 — Nick.Mc, Oct 23 '14 at 04:16
I created the variable for start time and end time of the Dataflow task, that loading the files into the table, and passes those variables into the table at the starting and ending of Dataflow task using an ExecuteSQL task.. Now it works fine .. Thank you All..:) — Hazeena A M, Oct 24 '14 at 05:37

score 3 · Answer 1 · edited May 23 '17 at 12:07

Create 2 global variables - one for start time and one for end time. Use a script task that runs before your foreach container to set the start time variable, and another script task that runs after your foreach container to set the end time variable. After that it's pretty simple to use a derived column Data Flow Transformation to get your start and end times into the pipe so that you can persist them.

In the script tasks don't forget to reference the variables, and the script should look something like this:

public void Main()
    {
        DateTime currentDate = DateTime.Now;
        Dts.Variables["StartDate"].Value = currentDate;
        Dts.TaskResult = (int)ScriptResults.Success;
    }

An alternative approach can also be found here: How can a step in an SSIS package check for the time? but this will only get you the start time, not the end time.

score 1 · Answer 2 · answered Oct 23 '14 at 12:24

I hope you are using SSIS 2012.

In the SSISDB, a set of views are available. Those gives us every details of the executed packages. You can edit from the below sql. (just an example)

SELECT  E.execution_id,
        E.project_name,
        E.package_name,
        S.start_time,
        S.end_time
FROM [SSISDB].[catalog].[executions] E
JOIN [SSISDB].[catalog].[executables] EX ON E.execution_id = EX.executable_id
JOIN [SSISDB].[catalog].[executable_statistics] S ON E.execution_id = S.execution_id

GBGOLC · Answer 3 · 2018-05-10T13:04:41.430

There's not really an elegant solution to this problem however, you can try this.

Create separate package containing single or group contained tasks, load and execute it from within a script task monitoring the Stopwatch object.

    public void Main()
    {
        Application dtsRuntimeApp = new Application();
        Package dtsPackage = dtsRuntimeApp.LoadPackage(@"PackageFileName.dtsx", null);
        Stopwatch stopwatch = new Stopwatch();
        stopwatch.Start();
        dtsPackage.Execute();
        stopwatch.Stop();
        var totalElapsedTime = stopwatch.Elapsed; // package timing here.
    }

How to get the start time and end time of process ssis

3 Answers3