Step to create Audit Task Summary package in SSIS
Get DFT Task and name it as "DFT_Load DBName_AuditTaskSummary".
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:
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.
,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.
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.