SQL Server Integration Services – Performing Audit in SSIS

Hi Friends,

In this blog post, let’s have an insight on how Auditing can be done in SQL Server Integration Services packages?

The basic auditing in package includes measures like

How many rows were inserted, updated or deleted, package name, status, etc. The information to be captured depends on personal choice or project needs.

So, Let’s create a simple SSIS package. In the post,  much of the focus is on audit instaed of transformations.

Let’s create a source and destination connection managers.

1_Performing_Audit_in_SQL_Server_Integration_Services

Now, drag and drop a Data flow task in Control flow and edit it.

2_Performing_Audit_in_SQL_Server_Integration_Services

In the Data flow tab, drag and drop an OLE DB source and extract the data from required table.

3_Performing_Audit_in_SQL_Server_Integration_Services

Perform all the necessary transformations. In the example i have taken “RowCount” transformation and simply mapped the source columns and destination columns. You can use “RowCount” transformation after using lookup tranformation to get the number of rows inserted / updated.

4_Performing_Audit_in_SQL_Server_Integration_Services

Create a Variable “intRowCount” of type Int32 of scope of Package. This variable is used to store number of rows extracted.

Now, take a destination transformation. In this case, since its my local machine and destination is SQL Server. Hence, I have taken SQL Server destination. Make sure if you use SQL Server destionation, then the destination should be your local system only and you create Target connection manager specifying (localhost or . (dot )).

5_Performing_Audit_in_SQL_Server_Integration_Services

Now, In Control flow drag and drop an Execute SQL task and perform some updates while connecting it to the Data Flow task.

Note: The Execute SQL task taken here is just for reference. You can take any task as per your requirement. The basic purpose here is to show Auditing.

6_Performing_Audit_in_SQL_Server_Integration_Services

Now comes the important part. Let’s create a table in SQL Server to hold the Audit information.

7_Performing_Audit_in_SQL_Server_Integration_Services

In this case i have created a separate schema “Audit” in my target database.

Now, Create the below variables of scope package.

  1. “StringStatusSuccess” of type String and Value “Success”.
  2. “StringStatusFailure” of type String and Value “Failure”.

8_Performing_Audit_in_SQL_Server_Integration_Services

3. “StringPackageVersion” of type string and set expression as below.

9_Performing_Audit_in_SQL_Server_Integration_Services

In the expression concatenate the System Variable “PackageName” and Syatem Variable “VersionBuild”.

Now, drag and drop an Execure SQL task in control flow, rename it as “Audit Info On Success” and edit it.

   

10_Performing_Audit_in_SQL_Server_Integration_Services

So, the information captured here in package would go infor my Audit table I created above.

11_Performing_Audit_in_SQL_Server_Integration_Services

Notice that, In the Parameter Mapping tab of my Execute SQL Task I have mapped some of the parameters to system variables and rest to the variables I created above.

In the Parameter mapping I have taken variable “StringStatusSuccess” as this indicates the status of package as Success.

Now, drag and drop another Execute SQL task in control flow, rename it as “Audit Info On Failure” and edit it.

12_Performing_Audit_in_SQL_Server_Integration_Services

 

13_Performing_Audit_in_SQL_Server_Integration_Services

The Execute SQL task is same as the above except for a variable “StringStatusFailure” as this will execute on Failure in package.

Now, Connect all the tasks to the Execute SQL task “Audit Info On Failure” and edit the precedence constraints connecting to this task. Change the Value to “Failure”.  Select the “Logical OR” option in Multiple Constarints.

14_Performing_Audit_in_SQL_Server_Integration_Services

Check the Audit table before executing the package.

15_Performing_Audit_in_SQL_Server_Integration_Services

Now, execute the package and check the Audit table again.

16_Performing_Audit_in_SQL_Server_Integration_Services

17_Performing_Audit_in_SQL_Server_Integration_Services

Now, Let’s deliberately create some error in “Update Destination” Execute SQL task and execute the package and check the Audit table again.

18_Performing_Audit_in_SQL_Server_Integration_Services

19_Performing_Audit_in_SQL_Server_Integration_Services

 

Happy Learning!!!

 

Regards

Mridul Chandhok

Like us on FaceBook Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook

   

About Mridul Chandhok

MSBI Developer having 4 years of experience in Report Writing using SSRS. Writing ETL packages for both OLTP and DW databases Using SSIS with focus on package optimization. In free time I love to explore new stuff on SQL Server. Love Bicycling and Swimming. Die Hard Fan of Heavy Metal Music.

View all posts by Mridul Chandhok →

2 Comments on “SQL Server Integration Services – Performing Audit in SSIS”

Leave a Reply

Your email address will not be published.