Performing Audit Function in Event Handlers in SSIS Package

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
"