The Microsoft SQL Server Integration Services (SSIS) management framework was developed to address a multitude of SSIS development and management issues. The framework was designed to streamline not only development but also development practices and standards. It institutes a methodology that puts standards and practices into place so that the process of development becomes structured around a set of principles, thereby minimizing the vast differences in development styles.

The framework also implements an organized structure for managing SSIS, SSIS package execution, and performance metrics. It provides detailed information that can be tracked and used for extensive, informative reporting. Key pieces of the reporting portion of the management framework provide in-depth insight to alert and identify possible problem areas or problematic packages, in addition to supplying statistics for performance metrics. Additionally, the SSIS management framework reports provide detailed roadmaps for troubleshooting, as well as a mechanism to standardize the set of steps to be taken in the troubleshooting process.

We have identified the following problems when working with SSIS on different clients:

  1. Multiple Development Styles – Many organizations have several developers, teams, and divisions creating SSIS packages. SSIS is not only used to develop ETL packages, but it can also be used for different types of data movement or to automate repeating processes for data management. The existence of so many variables with different work styles, as well as the lack of development standards (within a company), often results in many flavors of packages across various divisions―and sometimes within a single team or project.
  2. Inconsistent Configuration Management – Again, this often varies in style by developer, teams, and divisions. Some teams might use configuration-management procedures, whereas others teams will not. The challenge this brings up is not being able to clearly mange changes in the environment, such as a database being moved to a different server and ensuring that all packages that access that database will automatically point to the new location.
  3. Multiple Configuration Locations – It is not uncommon to have more than one dtsconfig file on a single server or to have one dtsconfig file per SSIS package. The multiple configuration locations make it extremely difficult to manage configuration changes. The purpose of configuration management is to centralize and standardize the management of SSIS packages across teams, departments, divisions, and organizations.
  4. No Centralized Monitoring – It is not uncommon for an organization to have several SSIS packages running daily, often on different servers. When a package fails, it is usually very difficult to identify what caused the failure of the package, due to inconsistent audit controls. It is even possible that a package has failed many times for over a month, but proper error and audit controls were not in place (within the package) to notify appropriate staff of the error. Obviously, these scenarios create exposure to the department and people responsible for managing SSIS.
  5. Reactive Problem Notification – In the absence of good monitoring, problems often become overlooked or are not discovered until the end users find the issues and notify IT. Sometimes, this notification can be too late to recover some data changes.
  6. Inconsistent Problem Solving Methods – The methods will depend on the individual trying to solve the problem. A developer of the package might well know (quickly) how to identify where the package has failed. However, it is not uncommon that companies manage packages that were written by outside consultants. This leaves room for exposure in not having pre-defined methods or standards to problem solving.
  7. Lack of Adequate and Appropriate Information – It is not uncommon for a package to fail and not specifically indicate reasons for the failure. The lack of standards in development and management create worst-case scenarios when attempting to problem-solve package failures.

Areas of Standardization in SSIS

To avoid problems, the following areas can be standardized throughout an organization for more consistent use of SSIS:

  1. Management Configurations – Identifying configuration standards will create consistency across the organization in the development process. This standardization will make it easier to manage configurable objects within SISS packages.
  2. Package and Process Audit
  3. Package Audit – Implements the use of package version and package runtime information to be able to identify and track version modifications and package execution.
  4. Process Management Audit – Implements process-level auditing, which provides information pertaining to a set of packages that implement a process, such as a sales data mart ETL process. Organizing and standardizing this information will provide process-level auditing, such as success and failure of a process and package(s) within the process. Knowing that an ETL process failed or has significantly expanded in its runtime can provide valuable insight for process management.
  5. Common Logging Infrastructure – Identifies a common set of standardization rules with which to log package execution details. This logged information provides roadmaps to troubleshooting, performance trend analysis, and package runtime analysis.
  6. Common Error Management – Identifies standards within package design of how to manage and report package errors. This type of standardization ensures the capturing of errors, thus providing a framework to organize and report from this information.
  7. Performance Monitoring and Troubleshooting – Implementing management configurations and package and process auditing will provide an information-rich structure to be able to better monitor the performance of the SSIS packages that are running in the environment and to provide highly visible and standard roadmaps in the troubleshooting process.

These standards will provide us with a very important set of benefits:

  1. Simplified Monitoring – Provides a streamlined approach to managing and monitoring the execution of SSIS packages.
  2. Easy Access for Troubleshooting Information – Among other data, the SSIS management framework provides auditing of SSIS package execution time for performance trending, as well as identifies version change at the package level. The framework also provides a standard approach to identifying failure, lending an easy-to-follow roadmap to pinpoint the root of the problem or failure.
  3. Increased Developer Productivity – Creating SSIS templates with built-in standards and processes makes the development process more simple. The auditing and package-management functions are built into each SSIS package by utilizing pre-built templates. All necessary connection strings and standard functionality are pre-built, leaving only the core application of the package to be built. Best practices are ensured (for the organization) by implementing and utilizing a global set of standards, without having to depend on each developer to create the same set of standards within their packages. The utilization of templates automatically increases developer productivity and decreases the differentiating programming styles and approaches.
  4. Simplified Configuration Management – Eliminates the duplication of configuration information and provides an easy structure to move packages from development to quality assurance to production.

 Considerations

  1. Customization – To optimize the framework for an organization, it is important to identify the details required by the organization. Identify the current configuration and standards and how they can be improved upon by utilizing the features of the SSIS management framework. Organization-specific topics include the types of systems, types of users, and type of security implemented. There are many steps involved to customize an organizational SSIS management framework, and identifying these steps and planning are crucial to a successful implementation.
  2. Framework Implementation in Organization – The next step is to consider at which level in the organization the SSIS management framework is applicable.
    a) Application Level – The framework can be implemented at an application level to standardize development for a particular project. However, this is not recommended, because it negates the idea of having organizational standards and policies.
  3. Department – The framework can be implemented at a department level to standardize development within a department. This can be beneficial to a department, because it creates department standards and policies. This can be useful in environments where there is sensitive data from one department to another, thus requiring different instances of the SSIS management framework to be installed in several departments.
  4. Organization – Ideally, implementing the SSIS management framework at an organization level will provide the most conclusive snapshot of SSIS package performance and provide significant information on troubleshooting and performance metrics.
  5. Implementing an ETL Process Framework – This can be considered as an add-on to the SSIS management framework. The ETL process framework provides a greater ability to utilize the metadata when loading dimensions and fact tables, as well as the extracts and the incremental extraction of data. It is built with the same concept of the utilization of templates.
  6. Incorporating Alerts – One of the main focuses of the SSIS management framework is to provide a proactive approach to SSIS monitoring and management. It provides information that can point to possible future performance issues, and it provides roadmaps to quickly resolve broken or failed packages. These alerts can be programmed into the framework to act as pre-notification of potential issues.

 Getting Started

  1. Determine Process Owner – It is important to determine the individual who will be the owner of the process from start to finish.
  2. Determine Needs
  3. Components – What components of the framework will be needed in the organization?
  4. Customization – How will these components be customized to the organization?
  5. Organization Policies – What are the security policies within the organization? For example: Are the use of environment variables or XML file configurations permissible within the organization’s environment?
  6. Design Common Management Database – Design the SSIS management framework database(s) customized to the organization.
  7. Develop Template Package and Management Reports – Develop the template package to include additional connection manager objects, as well as other organization-specific objects, such as variables. Develop the management reports that will provide detailed information on the customized framework.
  8. Develop Framework Use Documentation – First, document the templates for the developers so that they understand the framework and understand how to utilize the templates. Next, document the framework from the standpoint of the personnel who will manage and monitor the system. This would include how to monitor, how to analyze, how to troubleshoot, and how to utilize all the tools of the SSIS management framework.
  9. Develop a Deployment Strategy – Identify deployment strategies from an organization standpoint down to a departmental level.
  10. Implement Framework – Begin to utilize the framework in the live development environment, utilizing the template features and tools of the customized version.
  11. Create Migration Plan for Existing Packages – Develop a plan to migrate the existing packages. Identify the top-priority packages to be migrated and then work from a top-down list of most important packages to migrate.
  12. Train Developers and Administrators – Train the developers and administrators utilizing the documentation.

 Conclusion

We can easily identify all the areas we need to focus on to ensure that we have consistent ETL development within all our BI projects. That will help us speed up new development, along with facilitating the maintenance tasks of the existing projects. In subsequent articles, we will show how we have standardized these areas.

This article has a second part.