Steps and scripts to create Audit function in Event Handlers tab in a SSIS package.
Create variables in package for declared
values
--In the Event
Handlers tab – do the below steps
Select "OnTaskFailed" - get
Script Task and name it as 'SRC SetOnTaskFailed'– in the
ReadWriteVariables option – select "User::Status" and
this User::Status should be created in variable as "Status"
and the value as "Completed".
And go to Edit Script option – write
the below query
public
void
Main()
{
//
TODO: Add your code here
Dts.Variables["User::Status"].Value
= "OnTaskFailed";
Dts.TaskResult =
(int)ScriptResults.Success;
}
Select "OnError" - get
Script Task and name it as 'SRC SetOnError'– in the
ReadWriteVariables option – select "User::Status" and
this User::Status should be created in variable as "Status"
and the value as "Completed".
And go to Edit Script option – write
the below query
public
void
Main()
{
//
TODO: Add your code here
Dts.Variables["User::Status"].Value
= "OnError";
Dts.TaskResult =
(int)ScriptResults.Success;
}
From the SRC
SetOnError – get a line to connect an Exe SQL task and name it as
'SQL Recreate PK_Index_of_Target'.
In connector –
select the Evalution Operation as "Constraint"
and Value as
"Success".
Also select the
Multiple Constraints with "OR" option.
And connect line
to – "SQL Recreate PK_Index_of_Target" (Exe SQL Task)
In which, do as
below:
--write the below
script to insert values in Audit
EXEC
[dbo].[sp_ExecDropAndRebuildSql] ?, ?, 'create', 'p,i'
And go the Parameter Mapping option and
select as below
variableName Direction DataType ParameterName ParameterSize
User::SchemaName Input Varchar 0 -1
User::TableName Input Varchar 1 -1
Select "OnPreExecute" – get
Script Task - In the ReadOnlyVariables option – select
"System::SourceName,Uder::AuditId,System::PackageName" and
in the ReadWriteVariables option – select
"User::IsPackageLevel,User::IsParentValue" and this
User::IsPackageLevel,User::IsParentValue and UserAuditId should be
created in variable as "IsPackageLevel" with value as
"False","IsParentValue" with value as "False"
and "AuditId" with value as "-1".
And go to Edit Script option – write
the below query
public
void
Main()
{
//
TODO: Add your code here
if
(Dts.Variables["System::SourceName"].Value.Equals(Dts.Variables["System::PackageName"].Value))
{
Dts.Variables["User::IsPackageLevel"].Value
= true;
if
((int)Dts.Variables["User::AuditId"].Value
== -1)
{
Dts.Variables["User::IsParentValue"].Value
= false;
}
else
{
Dts.Variables["User::IsParentValue"].Value
= true;
}
}
else
{
Dts.Variables["User::IsPackageLevel"].Value
= false;
}
Dts.TaskResult =
(int)ScriptResults.Success;
}
From the SRC
Intial – get two line to connect two Exe SQL task
"SQL
Insert [DBName]_Audit (AND) SQL Insert [DBName]_AuditTask"
In connector I
– select the Evalution Operation as "Expression and
Constraint"
and Value as
"Success" and Expression as "@IsPackageLevel &&
!@IsParentValue".
Also select the
Multiple Constraints with "AND" option.
And connect line
to – "SQL Insert Imports_Audit" (Exe SQL Task)
In which, do as
below:
--write the below
script to insert values in Audit
exec
spu_insert_audit ?,?, ?,?,?,? OUT
And go the Parameter Mapping option and
select as below
variable
Name Direction DataType ParameterName ParameterSize
User::AuditId Output Long 5 -1
System::StartTime Input Date 0 -1
System::Versionbuild Input Long 1 -1
System::UserName Input Varchar 2 -1
System::PackageName
Input Varchar 3 -1
System::MachineName
Input Varchar 4 -1
In connector
II – select the Evalution Operation as "Expression and
Constraint"
and Value as
"Success" and Expression as
"!(SUBSTRING(@[System::SourceName],1,4)=="FELC")
&& ! (SUBSTRING(@[System::SourceName],1,3)=="SRC")
&& !@IsPackageLevel".
Also select the
Multiple Constraints with "AND" option.
And connect line
to – "SQL Update Imports_AuditTask" (Exe SQL Task)
In which, do as
below:
--write the below
script to insert values in AuditTask
exec
spu_insert_audit_task ?,?,?,?,?,?,?,?,? OUT
And go the Parameter Mapping option and
select as below
variable
Name Direction DataType ParameterName ParameterSize
User::AuditId Input Long 0 -1
User::AuditTaskId Output Long 8 -1
System::StartTime Input Date 1 -1
System::Versionbuild Input Long 2 -1
System::UserName Input Varchar 3 -1
System::PackageName Input Varchar 4 -1
System::MachineName Input Varchar 5 -1
System::SourceName Input Varchar 6 -1
User::TableName Input Varchar 7 -1
Select "OnPostExecute" –
get Script Task - In the ReadOnlyVariable option – select
"System::SourceName,System::PackageName" and in the
ReadWriteVariables option – select "User::IsPackageLevel"
and this User::IsPackageLevel should be created in variable as
"IsPackageLevel" and the value as "False".
And go to Edit Script option – write
the below query
public
void
Main()
{
//
TODO: Add your code here
if
(Dts.Variables["System::SourceName"].Value.Equals(Dts.Variables["System::PackageName"].Value))
{
Dts.Variables["User::IsPackageLevel"].Value
= true;
}
else
{
Dts.Variables["User::IsPackageLevel"].Value
= false;
}
Dts.TaskResult =
(int)ScriptResults.Success;
}
From the SRC
IsPackageLevel – get two line to connect two Exe SQL task
"SQL
Update [DBName]_Audit (AND) SQL Update [DBName]_AuditTask"
In connector I
– select the Evalution Operation as "Expression and
Constraint"
and Value as
"Success" and Expression as "@IsPackageLevel &&
!@IsParentValue".
Also select the
Multiple Constraints with "AND" option.
And connect line
to – "SQL Update Imports_Audit" (Exe SQL Task)
In which, do as
below:
--write the below
script to update values in Audit
UPDATE
[dbo].[Audit]
SET [Status]
= 'Completed'
,[EndTime] = GETDATE()
Where
AuditId= -1
GO
Go to Expression
– select the "SqlStatementSource" option and write below
query:
"UPDATE
[dbo].[Audit]
SET [Status] = '"+ @[User::Status] +"'
,[EndTime] = GETDATE()
Where AuditId= "+ (DT_WSTR,
20) (@[User::AuditId]) +"
GO
"
In connector
II – select the Evalution Operation as "Expression and
Constraint"
and Value as
"Success" and Expression as "!@IsPackageLevel".
Also select the
Multiple Constraints with "AND" option.
And connect line
to – "SQL Update Imports_AuditTask" (Exe SQL Task)
In which, do as
below:
--write the below
script to update values in AuditTask
UPDATE
[dbo].[AuditTask]
SET [Status]
= 'Completed'
,[EndTime] = GETDATE()
,[RowsAffected]= 0
,[LastModDate]=''
Where
AuditTaskId= '0'
GO
Go to Expression
– select the "SqlStatementSource" option and write below
query:
"UPDATE [dbo].[AuditTask]
SET [Status] = '"+@[User::Status]+"'
,[EndTime] =
GETDATE()
,[RowsAffected]= "+ (DT_WSTR, 20)(
@[User::RowsAffected]) +"
,[LastModDate]='"+@[User::LastModDate] +"'
Where
AuditTaskId= '"+ (DT_WSTR, 20)(@[User::AuditTaskId]) +"'
GO"
==================================================
To
Create Audit function in Control flow
--In the Event
Handlers tab – do the below steps
Select "OnTaskFailed"
- get Script Task – in the ReadWriteVariables option – select
"User::Status" and this User::Status should be created in
variable as "Status" and the value as "Completed".
And go to Edit Script option – write
the below query
public
void
Main()
{
//
TODO: Add your code here
Dts.Variables["User::Status"].Value
= "OnTaskFailed";
Dts.TaskResult =
(int)ScriptResults.Success;
}
Select "OnError" -
get Script Task – in the ReadWriteVariables option – select
"User::Status" and this User::Status should be created in
variable as "Status" and the value as "Completed".
And go to Edit Script option – write
the below query
public
void
Main()
{
//
TODO: Add your code here
Dts.Variables["User::Status"].Value
= "OnError";
Dts.TaskResult =
(int)ScriptResults.Success;
}
Select "OnPostExecute"
– get Script Task - In the ReadOnlyVariable option – select
"System::SourceName,System::PackageName" and in the
ReadWriteVariables option – select "User::IsPackageLevel"
and this User::IsPackageLevel should be created in variable as
"IsPackageLevel" and the value as "False".
And go to Edit Script option – write
the below query
public
void
Main()
{
//
TODO: Add your code here
if
(Dts.Variables["System::SourceName"].Value.Equals(Dts.Variables["System::PackageName"].Value))
{
Dts.Variables["User::IsPackageLevel"].Value
= true;
}
else
{
Dts.Variables["User::IsPackageLevel"].Value
= false;
}
Dts.TaskResult =
(int)ScriptResults.Success;
}
From the SRC
IsPackageLevel – get a line to connect an Exe SQL task
"SQL
Update [DB Name]_Audit "
In connector –
select the Evalution Operation as "Expression"
and Value as
"Success" and Expression as "@IsPackageLevel ||
@Status!="Completed"".
Also select the
Multiple Constraints with "OR" option.
And connect line
to – "SQL Update [DB Name]_Audit " (Exe SQL Task)
In which, do as
below:
--write the below
script to update values in Audit
UPDATE
[dbo].[Audit]
SET [Status]
= 'Completed'
,[EndTime] = GETDATE()
Where
AuditId= 0
GO
Go to Expression
– select the "SqlStatementSource" option and write below
query:
"UPDATE [dbo].[Audit]
SET [Status] =
'"+ @[User::Status] +"'
,[EndTime] = GETDATE()
Where AuditId= "+ (DT_WSTR, 20) (@[User::AuditId]) +"
GO
"
