- C# Scripts
- DWH INTERVIEW QUESTIONS
- MS Dynamics AX 2012 R2 Video Tutorial
- Project / Work Support
- SQL SERVER DBA INTERVIEW QUESTIONS
- SQL SERVER DBA Video Tutorial
- SQL Server / TSQL Tutorial
- SQL Server 2016
- SQL Server Scripts
- SSIS INTERVIEW QUESTIONS
- SSIS Video Tutorial
- SSRS INTERVIEW QUESTIONS
- SSRS Video Tutorial
- TSQL INTERVIEW QUESTIONS
- Team Foundation Server 2013 Video Tutorial
- Team Foundation Server 2015 Video Tutorial
- Windows 10
- Windows Server 2012 R2 Installation Videos
SSIS - Log Package Execution Time To Audit Table
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),
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
Create a variable that can hold the start date time for us that we can use at the end for Audit purpose.
Set the value of variable by using Execute SQL task as shown below
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.
Bring Execute SQL Task that we will use to insert information into out log table. Configure as shown below
"insert into dbo.PackageExecutionLog(PackageName,TimeTakeninMinutes,ExecutionDateTime)
Select '"+ @[System::PackageName]+"',datediff(minute,'"+(DT_STR,50,1252) @[User::VarStatTime]+"',getdate()),getdate()"
This is how our final package should look like
Lets Select some records from our dbo.PackageExeuctionLog table and see what information it inserted