Wednesday, June 27, 2012

Performing Audit Task Summary in SSIS Package

Step to create Audit Task Summary package in SSIS


Get DFT Task and name it as "DFT_Load DBName_AuditTaskSummary".
Get into the DFT task and Design package as given below:
Now get into the OLE_Source task and write the SQL command text as given below to get the audit data from AuditTask table.

SELECT GETDATE() AS SummaryDate
              ,MAX(AuditTaskId) AS maxAuditTaskId
              ,MAX(AuditId) AS MaxAuditId
              ,PackageName
             ,TableName
 FROM dbo.AuditTask
 GROUP BY PackageName , TableName


Next, go to lookup task and name it as 'LKP AuditTaskSummary and set as "Redirect rows to no match output" to handle rows with no matching entries.
Go to 'Connection' option and write the below query in "Use results of an SQL query:" filed.


SELECT TableName
              ,PackageName
  FROM dbo.AuditTaskSummary


Next go to the 'Columns' option and map the cloumns as shown below:
From Lookup task, connect the Lookup Match Output to 'OLE DB Command' task. In that task, go the 'Component Properties' section and write the below query in the SqlCommand field.

Update dbo.AuditTaskSummary 
 Set SummaryDate = getDate(),
       MaxAuditTaskId = ?
where tableName = ? and PackageName = ?


And go to 'Column Mapping' section and map the columns as shown below. Check the exact data type match before map it.
Next, from the Lookup task, connect the Lookup No Match Output to 'OLE Destination named as 'OLE Dest_AuditTaskSummary' through Row Count task. Select the 'dbo.AuditTaskSummary' table and map the columns accordingly.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.