Extract audit data from SSIS Execution Log (EventHandler and Regular Expression)

Introduction

In our last post (Regex Cheat Sheet) we explained use cases of SSIS Regular Expression Parser Task . Now lets look at some real world usecase. Basically in this article we are going to Extract data from audit log using SSIS. For demo purpose we will use log generated by SSIS Export CSV File Task output. When you export many tables dynamically using this task you want to know how many Row count exported for each Table. This information is logged in SSIS Output Log but no easy way to capture in SSIS variable. So what we will do we will use few tricks to capture that data using Regular Expression usecase.

So let’s get started.

Prerequisites

Before we perform steps listed in this article, you will need to make sure following prerequisites are met:
  1. SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
  2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
  3. Make sure ZappySys SSIS PowerPack is installed (download it).
  4. Optional (If you want to Deploy and Schedule ) - Deploy and Schedule SSIS Packages

Setup Export CSV Task (Output Multiple Tables to CSV Files Dynamically)

For example purpose we will use Export CSV File Task but you can use this technique for any other Tasks.

  1. Drag and Drop ZS Export CSV Task from SSIS Toolbox and configure as below.
    SSIS Export to CSV File Task - Drag and Drop
  2.   Configure Export CSV Task to output multiple tables as explained here
  3. Now create 2 SSIS User Variables
    1. FilePath (String Type)
    2. RowCount (String Type)

That’s it… now in the next section we will setup event handler to capture output from Log.

Setup EventHandle / Extract Audit Data using Regex

  1. Now select task and click Event Handler tab
  2. Select Executable Name from the dropdown, Select Event Name OnInformation from Handler Dropdown
  3. Click Create Hyper Link to Create Event Handler like below
    Create Task Level Event Handler in SSIS

    Create Task Level Event Handler in SSIS

  4. Now Drag below 3 tasks and connect like this
    1. Script Task (We will use as dummy start (No configuration needed)
    2. ZS Regular Expression Parser Task
    3. ZS Logging Task (Optional) but we will use to Output Extracted Values
  5. Connect Dummy Script Task to ZS Regular Expression Parser Task and Right click on Green connected Arrow and Use Expression as below. Click OK to save
  6. Configure ZS Regular Expression Parser Task as below
    1. Enter {{System::ErrorDescription}}  as Direct Value
    2. Enter Below two mappings
      1. For RowCount set
      2. For FilePath set
  7. Here is how it will look like
    Extract data from audit log using SSIS Regular Expression Parser Task

    Extract data from audit log using SSIS Regular Expression Parser Task

  8. Thats it… Now connect 2nd step to last Logging Task…. in that Task you can set Text like below just to log extracted data. You can also use Execute SQL Task to log into Database Table
  9. Now run Package and you will see audit data we extracted for each file. Once File is exported we capture OnInformation event… and if Message contains substring like “records written to” then we extract data into variable for Row Count and Path then you can save to desired place for auditing purpose

 

Posted in SSIS CSV Export Task, SSIS Logging Task, SSIS Regex Parser Task and tagged , , , , .