SSIS - Log Package Execution Time To Audit Table

Scenario: 

We are in process of creating our SSIS Package, one of the requirement is to Audit the information about package execution to below table

CREATE TABLE dbo.PackageExecutionLog

  (

     PkgID              INT IDENTITY(1, 1),

     PackageName        VARCHAR(100),

     ExecutionDateTime  DATETIME,

     TimeTakeninMinutes INT

  ) 

Solution :

As we can see that the important information that we need to log for each execution is Package Name,Execution Date Time and Total time taken in minutes by Package for execution.

Lets created our SSIS Package 

Step 1: 

Create a variable that can hold the start date time for us that we can use at the end for Audit purpose.

Step 2:

Set the value of variable by using Execute SQL task as shown below

Step 3:

Place all the other Tasks such as data flow task, execute sql task or any other those are required by your package.

I have put one data flow task and one execute sql task with statement WAITFOR DELAY '00:2:05' to make it wait for couple of minutes before moving to next task.

Step 4:

Bring Execute SQL Task that we will use to insert information into out log table. Configure as shown below

Expressions:

"insert into dbo.PackageExecutionLog(PackageName,TimeTakeninMinutes,ExecutionDateTime)

Select '"+ @[System::PackageName]+"',datediff(minute,'"+(DT_STR,50,1252) @[User::VarStatTime]+"',getdate()),getdate()"

Final Output: 

This is how our final package should look like

Lets Select some records from our dbo.PackageExeuctionLog table and see what information it inserted