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.

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

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

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.

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 )).

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.

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

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”.

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

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.

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

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.

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.

Check the Audit table before executing the package.

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

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

Happy Learning!!!