{"id":8928,"date":"2020-05-26T20:11:33","date_gmt":"2020-05-26T20:11:33","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=8928"},"modified":"2020-05-26T20:19:41","modified_gmt":"2020-05-26T20:19:41","slug":"extract-audit-data-ssis-execution-log-eventhandler-regular-expression","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/extract-audit-data-ssis-execution-log-eventhandler-regular-expression\/","title":{"rendered":"Extract audit data from SSIS Execution Log (EventHandler and Regular Expression)"},"content":{"rendered":"

Introduction<\/h2>\n

In our last post<\/a>\u00a0(Regex Cheat Sheet) we explained use cases of SSIS Regular Expression Parser Task<\/a>\u00a0. 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\u00a0SSIS Export CSV File Task output<\/a>. 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.<\/p>\n

So let’s get started.<\/p>\n

Prerequisites<\/span><\/h2>\r\nBefore we perform the steps listed in this article, you will need to make sure the following prerequisites are met:\r\n
    \r\n \t
  1. SSIS<\/abbr> designer installed.\u00a0Sometimes it is referred to as BIDS<\/abbr> or SSDT<\/abbr> (download it from the Microsoft site<\/a>).<\/li>\r\n \t
  2. Basic knowledge of SSIS package\u00a0development using\u00a0Microsoft SQL Server Integration Services<\/em>.<\/li>\r\n \t
  3. Make sure\u00a0ZappySys SSIS PowerPack<\/a><\/span>\u00a0is installed (download it<\/a>, if you haven't already).<\/li>\r\n \t
  4. (Optional step<\/em>).<\/em>\u00a0Read this article<\/a>, if you are planning to deploy packages to a server and schedule their execution later.<\/li>\r\n<\/ol><\/div>\n

    Setup Export CSV Task (Output Multiple Tables to CSV Files Dynamically)<\/h2>\n

    For example purpose we will use Export CSV File Task but you can use this technique for any other Tasks.<\/p>\n

      \n
    1. Drag and Drop ZS Export CSV Task from SSIS Toolbox and configure as below.
      \n\"SSIS<\/li>\n
    2. \u00a0 Configure Export CSV Task to output multiple tables as explained here<\/a><\/li>\n
    3. Now create 2 SSIS User Variables\n
        \n
      1. FilePath<\/strong> (String Type)<\/li>\n
      2. RowCount<\/strong> (String Type)<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n

        That’s it… now in the next section we will setup event handler to capture output from Log.<\/p>\n

        Setup EventHandle \/ Extract Audit Data using Regex<\/h2>\n
          \n
        1. Now select task and click\u00a0Event Handler tab<\/strong><\/li>\n
        2. Select Executable Name<\/strong> from the dropdown, Select Event Name OnInformation<\/strong> from Handler Dropdown<\/li>\n
        3. Click Create Hyper Link to Create Event Handler like below\n
        4. Now Drag below 3 tasks and connect like this\n
            \n
          1. Script Task (We will use as dummy start (No configuration needed)<\/li>\n
          2. ZS Regular Expression Parser Task<\/li>\n
          3. ZS Logging Task (Optional) but we will use to Output Extracted Values
            \n            \"\"<\/a><\/li>\n<\/ol>\n<\/li>\n
          4. 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<\/li>\n
          5. Configure\u00a0ZS Regular Expression Parser Task as below\n
              \n
            1. Enter {{System::ErrorDescription}}<\/span>\u00a0 as Direct Value<\/li>\n
            2. Enter Below two mappings\n
                \n
              1. For RowCount set\n
                Total (\\d+) records written to : (.*){{1,1}}<\/pre>\n<\/li>\n
              2. For FilePath set\n
                Total (\\d+) records written to : (.*){{1,2}}<\/pre>\n<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n
              3. Here is how it will look like\n
              4. 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\n
                {{User::FilePath}} ===> {{User::RowCount}}<\/pre>\n<\/li>\n
              5. 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\"\"<\/a><\/li>\n<\/ol>\n
                 <\/p>\n","protected":false},"excerpt":{"rendered":"
                Introduction In our last post\u00a0(Regex Cheat Sheet) we explained use cases of SSIS Regular Expression Parser Task\u00a0. 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\u00a0SSIS Export CSV File Task output. When […]<\/p>\n","protected":false},"author":1,"featured_media":8929,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[127,145,133],"tags":[582,583,161,95,12],"yoast_head":"\r\nExtract audit data from SSIS Execution Log (EventHandler and Regular Expression) | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"Learn how to extract audit data from SSIS Execution Log using Regular Expression Parser Task. We will extract Row Count \/ File Path using Task Event Handler\" \/>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/zappysys.com\/blog\/extract-audit-data-ssis-execution-log-eventhandler-regular-expression\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"Extract audit data from SSIS Execution Log (EventHandler and Regular Expression) | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"Learn how to extract audit data from SSIS Execution Log using Regular Expression Parser Task. We will extract Row Count \/ File Path using Task Event Handler\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/extract-audit-data-ssis-execution-log-eventhandler-regular-expression\/\" \/>\r\n<meta property=\"og:site_name\" content=\"ZappySys Blog\" \/>\r\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/ZappySys\/\" \/>\r\n<meta property=\"article:published_time\" content=\"2020-05-26T20:11:33+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2020-05-26T20:19:41+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2020\/05\/ssis-create-eventhandler.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"655\" \/>\r\n\t<meta property=\"og:image:height\" content=\"260\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\r\n<meta name=\"author\" content=\"ZappySys\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:creator\" content=\"@https:\/\/twitter.com\/zappysys\/\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ZappySys\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/extract-audit-data-ssis-execution-log-eventhandler-regular-expression\/\",\"url\":\"https:\/\/zappysys.com\/blog\/extract-audit-data-ssis-execution-log-eventhandler-regular-expression\/\",\"name\":\"Extract audit data from SSIS Execution Log (EventHandler and Regular Expression) | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/extract-audit-data-ssis-execution-log-eventhandler-regular-expression\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/extract-audit-data-ssis-execution-log-eventhandler-regular-expression\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2020\/05\/ssis-create-eventhandler.png\",\"datePublished\":\"2020-05-26T20:11:33+00:00\",\"dateModified\":\"2020-05-26T20:19:41+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\"},\"description\":\"Learn how to extract audit data from SSIS Execution Log using Regular Expression Parser Task. We will extract Row Count \/ File Path using Task Event Handler\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/extract-audit-data-ssis-execution-log-eventhandler-regular-expression\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/extract-audit-data-ssis-execution-log-eventhandler-regular-expression\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/extract-audit-data-ssis-execution-log-eventhandler-regular-expression\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2020\/05\/ssis-create-eventhandler.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2020\/05\/ssis-create-eventhandler.png\",\"width\":655,\"height\":260,\"caption\":\"Create Task Level Event Handler in SSIS\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/extract-audit-data-ssis-execution-log-eventhandler-regular-expression\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Extract audit data from SSIS Execution Log (EventHandler and Regular Expression)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/zappysys.com\/blog\/#website\",\"url\":\"https:\/\/zappysys.com\/blog\/\",\"name\":\"ZappySys Blog\",\"description\":\"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/zappysys.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\",\"name\":\"ZappySys\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"caption\":\"ZappySys\"},\"sameAs\":[\"http:\/\/www.zappysys.com\/\",\"https:\/\/www.facebook.com\/ZappySys\/\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/\"],\"url\":\"https:\/\/zappysys.com\/blog\/author\/admin\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Extract audit data from SSIS Execution Log (EventHandler and Regular Expression) | ZappySys Blog","description":"Learn how to extract audit data from SSIS Execution Log using Regular Expression Parser Task. We will extract Row Count \/ File Path using Task Event Handler","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/zappysys.com\/blog\/extract-audit-data-ssis-execution-log-eventhandler-regular-expression\/","og_locale":"en_US","og_type":"article","og_title":"Extract audit data from SSIS Execution Log (EventHandler and Regular Expression) | ZappySys Blog","og_description":"Learn how to extract audit data from SSIS Execution Log using Regular Expression Parser Task. We will extract Row Count \/ File Path using Task Event Handler","og_url":"https:\/\/zappysys.com\/blog\/extract-audit-data-ssis-execution-log-eventhandler-regular-expression\/","og_site_name":"ZappySys Blog","article_author":"https:\/\/www.facebook.com\/ZappySys\/","article_published_time":"2020-05-26T20:11:33+00:00","article_modified_time":"2020-05-26T20:19:41+00:00","og_image":[{"width":655,"height":260,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2020\/05\/ssis-create-eventhandler.png","type":"image\/png"}],"author":"ZappySys","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/zappysys\/","twitter_misc":{"Written by":"ZappySys","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/extract-audit-data-ssis-execution-log-eventhandler-regular-expression\/","url":"https:\/\/zappysys.com\/blog\/extract-audit-data-ssis-execution-log-eventhandler-regular-expression\/","name":"Extract audit data from SSIS Execution Log (EventHandler and Regular Expression) | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/extract-audit-data-ssis-execution-log-eventhandler-regular-expression\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/extract-audit-data-ssis-execution-log-eventhandler-regular-expression\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2020\/05\/ssis-create-eventhandler.png","datePublished":"2020-05-26T20:11:33+00:00","dateModified":"2020-05-26T20:19:41+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82"},"description":"Learn how to extract audit data from SSIS Execution Log using Regular Expression Parser Task. We will extract Row Count \/ File Path using Task Event Handler","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/extract-audit-data-ssis-execution-log-eventhandler-regular-expression\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/extract-audit-data-ssis-execution-log-eventhandler-regular-expression\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/extract-audit-data-ssis-execution-log-eventhandler-regular-expression\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2020\/05\/ssis-create-eventhandler.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2020\/05\/ssis-create-eventhandler.png","width":655,"height":260,"caption":"Create Task Level Event Handler in SSIS"},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/extract-audit-data-ssis-execution-log-eventhandler-regular-expression\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Extract audit data from SSIS Execution Log (EventHandler and Regular Expression)"}]},{"@type":"WebSite","@id":"https:\/\/zappysys.com\/blog\/#website","url":"https:\/\/zappysys.com\/blog\/","name":"ZappySys Blog","description":"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/zappysys.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82","name":"ZappySys","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","caption":"ZappySys"},"sameAs":["http:\/\/www.zappysys.com\/","https:\/\/www.facebook.com\/ZappySys\/","https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/"],"url":"https:\/\/zappysys.com\/blog\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/8928"}],"collection":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/comments?post=8928"}],"version-history":[{"count":2,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/8928\/revisions"}],"predecessor-version":[{"id":8934,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/8928\/revisions\/8934"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/8929"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=8928"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=8928"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=8928"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}