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
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.
ReplyDeleteSSIS Postgresql Write
Aivivu - đại lý chuyên vé máy bay trong nước và quốc tế
ReplyDeletekinh nghiệm mua vé máy bay đi Mỹ giá rẻ
gia ve tu my ve vietnam
tin tức các chuyến bay từ nhật về việt nam
vé máy bay từ đức về việt nam giá rẻ
vé máy bay từ canada về việt nam bao nhiêu tiền
gia ve may bay tu han quoc ve viet nam
khách sạn cách ly tphcm
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