{"id":4108,"date":"2017-10-24T11:30:31","date_gmt":"2017-10-24T10:30:31","guid":{"rendered":"http:\/\/andreas-wolter.com\/database-consulting-services\/ssis-logging-templates-best-practices-framework\/"},"modified":"2017-11-02T12:08:22","modified_gmt":"2017-11-02T11:08:22","slug":"ssis-logging-templates-best-practices-framework","status":"publish","type":"page","link":"https:\/\/andreas-wolter.com\/en\/database-consulting-services\/ssis-logging-templates-best-practices-framework\/","title":{"rendered":"SSIS Logging, Templates &#038; Best Practices Framework"},"content":{"rendered":"\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_heading-554a38036ba9a5999212a6bb0abd0680\">\n#top .av-special-heading.av-av_heading-554a38036ba9a5999212a6bb0abd0680{\npadding-bottom:10px;\n}\nbody .av-special-heading.av-av_heading-554a38036ba9a5999212a6bb0abd0680 .av-special-heading-tag .heading-char{\nfont-size:25px;\n}\n.av-special-heading.av-av_heading-554a38036ba9a5999212a6bb0abd0680 .av-subheading{\nfont-size:15px;\n}\n<\/style>\n<div  class='av-special-heading av-av_heading-554a38036ba9a5999212a6bb0abd0680 av-special-heading-h1 blockquote modern-quote modern-centered  avia-builder-el-0  el_before_av_textblock  avia-builder-el-first '><h1 class='av-special-heading-tag'  itemprop=\"headline\"  >SSIS Framework for professional environments<\/h1><div class=\"special-heading-border\"><div class=\"special-heading-inner-border\"><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/CreativeWork\" ><div class='avia_textblock'  itemprop=\"text\" ><p>Even with the improvements the SSIS DB has undergone since SQL Server 2012, protocolization is insufficient for professional enterprise data warehouse systems.<\/p>\n<p>A <strong>professional logging<\/strong> should help answer the following questions:<\/p>\n<ul>\n<li>With which package execution has data record X made it into the system?<\/li>\n<li>Which other data records are possibly affected by the same logic error as data record X?<\/li>\n<li>Which transformations have taken the longest time?<\/li>\n<li>Of X data records that were read from the data source, how many data records have been A) reinserted, B) changed, C) discarded during the data load process?<\/li>\n<\/ul>\n<p>The second aspect is that in practice, I see a lot of manually repeated development of SSIS packages. A professional <strong>template<\/strong>, already included by the according logging, saves the more time the more packages are developed, and additionally ensures <strong>consistency<\/strong>, which is essential for troubleshooting.<\/p>\n<p>More than<strong> 12 years of experience in the field of data warehouse<\/strong> with SQL Server, Integration and Analysis Services thus have produced a comprehensive framework including a <strong>best practices collection<\/strong>, which I can adapt according to the customer\u2019s requirements.<\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/CreativeWork\" ><div class='avia_textblock'  itemprop=\"text\" ><p>Features comprise:<\/p>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li>Standardized SSIS <strong>project template<\/strong> in Visual Studio<\/li>\n<li>Standardized SSIS <strong>package templates<\/strong> for <strong>SCD1, SCD2 and SCD6<\/strong> or master and fact tables<\/li>\n<li>Scripting for environment variables and assignment of these to the SSIS project in Integration Services<\/li>\n<li><strong>RAPID Development<\/strong> \u2013 up to 10 x less development work<\/li>\n<li>Standardized approach allows applications BIML, EziApi or XQuery<\/li>\n<li>Management and maintenance of all package parameters in a global SQL table as extension of the SSISDB<\/li>\n<li>Call of the packages by a designated Stored Procedure with automatic assignment of the parameters from the SQL table<\/li>\n<li>Merge statement for (initial) loading of the parameter table in accordance with dynamic and fixed parameters<\/li>\n<li>Incremental loading process by means of dynamic assignment of the relevant SSIS package parameters<\/li>\n<li>SSIS technique according to <strong>best practices for error detection and logging<\/strong> in the dataflow in accordance with transactions and <strong>best possible performance<\/strong><\/li>\n<li>Standardized <strong>naming conventions<\/strong> of all task- and dataflow components<\/li>\n<li>Transfer of a BatchID from the master package to the child packages for reporting and performance optimization<\/li>\n<li>Logging of the extracted and loaded number of data records permits <strong>control of the entire dataflow<\/strong><\/li>\n<li>Logging for changing variable values<\/li>\n<li>Logging of used connections<\/li>\n<li>Logging of errors of all Stored Procedures in the SSIS log used for the SSIS process, and storing in designated SQL log error table<\/li>\n<li><strong>Scheduling engine for packages<\/strong> and dependent parameter configuration (permits more precise execution of packages as SQL Server agent alone)<\/li>\n<li><b><strong>SSIS DB index optimization <\/strong><\/b><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/CreativeWork\" ><div class='avia_textblock'  itemprop=\"text\" ><p><strong>Learn more about the benefits and ask for your individual offer. <a href=\"https:\/\/andreas-wolter.com\/en\/contact\/\">Contact me here<\/a>. <\/strong><\/p>\n<\/div><\/section>","protected":false},"excerpt":{"rendered":"","protected":false},"author":4,"featured_media":0,"parent":4020,"menu_order":7,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-4108","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/pages\/4108","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/comments?post=4108"}],"version-history":[{"count":5,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/pages\/4108\/revisions"}],"predecessor-version":[{"id":4109,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/pages\/4108\/revisions\/4109"}],"up":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/pages\/4020"}],"wp:attachment":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media?parent=4108"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}