Chapter 14: SSIS General Principles


image from book Download CD Content

Overview

After completing this chapter, you will be able to:

  • Understand package design considerations.

  • Understand package component management.

  • Understand package component naming standards and policies.

  • Understand package deployment management.

This chapter will provide you with some learned techniques, proven best practices, and suggested approaches for your consideration when designing Microsoft SQL Server Integration Services (SSIS) packages and applications. You will most likely discover and define some best practices appropriate for your business requirements, too. The following list identifies the key initial steps for you to follow when designing applications using SSIS.

  • Understand your business requirements.

  • Understand your business processes.

  • Define your package usability.

  • Consider future package maintenance.

Before starting your application design, you need to understand the entire picture. When using SSIS for business integration and management applications, it is important to focus on the business requirements first. The next consideration is to understand your source business system's processes and dependencies and your application's need for staging data based upon the business application system processes. You learned in Chapter 13, "Populating Data Warehouse Structures," that there are many approaches and techniques for staging data for a variety of reasons. Only when all your business requirements and system processes are understood, established, and agreed upon should you go forward with designing and developing data migration, integration, and business intelligence (BI) applications. The design should consider the best types of SSIS tasks, components, and control flow processes to make optimal use of all parts of the application.

When you design the application packages for initial use, it is equally important to consider the package design for future maintenance in view of business and application changes. Often, this includes annotations, naming standards for SSIS package components, and other techniques you will learn about later in this chapter.

When using SSIS within data warehouse and BI systems, you also need to decide how to manage dimension tables with surrogate keys. The use of system-generated surrogate keys is a best practices discipline. Once the dimension table management is established, your attention will be focused on the fact tables to determine which level of data granularity is possible and desired.

In Chapter 13, you learned that the SQL Server 2005 SSIS data integration engine and data pipeline constitute a different architecture, requiring a different design strategy than for Data Transformation Services 2000. SSIS can perform data transforms in memory and in parallel, thus enhancing performance. Anytime you can design a package to minimize data writes (other than the final destination, of course), that is best for performance.

Eliminating disk data writes is not always available for your design. You might remember that you sometimes have to stage data that will require intermediate writing to disk. Most often, this is a characteristic of data availability and timing of business processes that provide the data for your application. You need to determine whether staged data needs to be persisted or can be temporarily stored for a specified time frame.




Microsoft SQL Server 2005 Integration Services Step by Step
MicrosoftВ® SQL Server(TM) 2005 Integration Services Step by Step
ISBN: 0735624054
EAN: 2147483647
Year: 2007
Pages: 152

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net