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

3 comments:

  1. There is always a need to look and grasp these kind of posts as much as possible which in a ways give out more and more information about PostgreSQL and SSIS.

    SSIS Postgresql Write

    ReplyDelete
  2. This study distinguished an expense hole of around $345 billion dollars and established that as a lot of 66% of this hole comes from entrepreneurs, business people, best in sg

    ReplyDelete