MSBI #71 – SSIS #30 – SQL Server 2012 – SSIS Design Patterns #1–Why , What ,Which and Subtypes Introduction of SSIS Design Patterns

Hi Friends,

Lets start with some new interesting series , as promised last month in this week we are starting with brand new series called : SSIS Design Patterns.

Before digging deep into SSIS  Design and Pattern we must understand concept of pattern .

I am going to explain that in this post because there all types of user who reads my blog’s and by gods grace I don’t want to loose them with heavy content of post.

So lets understand SSIS Design patterns which is one of the most important aspect for Developing any SSIS solution and its architecting perfectly and robust.

In this article we are going to cover

  • Why SSIS Design Pattern is required
  • What is meaning of Pattern
  • What is meaning for Design Pattern
  • Famous Quotes and Explanation  for Design Pattern
  • What information we have in Design patterns
  • Why to Use Design Pattern
  • What are SSIS Design Pattern
  • Which are Sub types of SSIS Design Pattern
  • SSIS Design Pattern Pyramid
  • Resource

Looking at every points above , now lets see in details Point by point as follow ..

Why SSIS Design Pattern is required  ?

SSIS Technology has outstanding growth from 2005 to 2012 release In which many small as well as large scale organisation have shown tremendous  interest SQL Server BI Stack which in result high acceptance and high evolution of SSIS

So during development of any solution in SSIS we must focus make our development stable on deciding which SSIS Design pattern should we use.

What is meaning of Pattern ?

A true meaning of Pattern itself is A model or original used as an archetype.

In computer software a design pattern is a reusable approach to solving a commonly-occurring software design problem. A design pattern is a template for solving a problem that can be used in many different situations

Patterns are a recent software engineering problem-solving discipline that emerged from the object-oriented community. Patterns have roots in many disciplines, including literate programming, and most notably in Alexander’s work on urban planning and building architecture. (Alexander, 1977).

What is meaning for Design Pattern ?

  • Patterns solves a problem: Patterns capture solutions, not just abstract principles or strategies.
  • Patterns is a proven concept: Patterns capture solutions with a track record, not theories or speculation.
  • Patterns describes a relationship: Patterns don’t just describe modules, but describe deeper system structures and mechanisms.
  • The pattern has a significant human component (minimize human intervention). A Design pattern defines a collection of patterns and the rules to combine them into an architectural style. Design pattern describe software frameworks or families of related systems.
    Famous Quotes and Explanation for Design Pattern  
    Following are most famous quote / thought related to patterns
  • “A pattern is a proven solution to a problem in a context”
  • “Each pattern is a three-part rule, which expresses a relation between a certain context, a problem, and a solution.” In which :

Problem: How do you allocate objects in memory?
Context: A large OO system in a virtual memory computer.
Solution: Run some typical problems and figure out which objects communicate frequently in a time locale and put them on the same page.

What information we have in Design patterns 

Following information :

  • Name that describes the pattern
  • Forces that could influence the problem or its solution
  • Solution proposed to the problem
  • Context for the solution
  • Rationale behind the solution
  • Known uses and related patterns
  • Problem to be solved by the pattern
  • Context, or settings, in which the problem occurs
  • Author and date information
  • References and keywords used or searching
  • Sample code related to the solution, if it helps This is what all patterns in general but we will move further only in focus of SSIS Design Pattern
    What are SSIS Design Pattern

There are four distinct pattern in SSIS as follow

  1. Common Design Patterns
  2. Lookup Design Patterns
  3. Parallel Design Patterns
  4. Advance Design Patterns In the next post we will have clear idea regarding why we have four SSIS Design pattern and all related information in second introductory post  

Which are Sub types of SSIS Design Pattern

Four SSIS Design pattern are further Divided into sub type for more understanding and its ease of Use

Common Design Patterns

  • Using The Database Engine
  • Avoiding Transaction
  • Change Data Capture
  • Using Multiple Transforms

Lookup Design Patterns

  • Surrogate Key Generation
  • Null Value Substitution
  • Late Arriving Facts
  • Change Detection

Parallel Design Patterns

  • Parallel Processing
  • Partitioning
  • Balance Data Distributer
  • Work Pile Pattern

Advance Design Patterns

  • Slowly Changing Dimensions
  • XML Patterns

We will discuss all above patterns as well in this series in my upcoming post !

SSIS Design Pattern Pyramid

    I have just formed this pyramid for simple understanding of this four pattern and how they are associated with each other as well as separated from each other



  • If you don’t want to wait till completion of this series you can go ahead and buy this book SSIS Design Patterns from authors Matt Masson,Tim Mitchell (Author), Jessica Moss (Author), Michelle Ufford (Author), Andy Leonard (Author)
  • Visit Matt Blog: imageMatt is really given lots of information related to this topic

    I wish I could buy that book and read it once through but ..

    Any ways still you can also wait until my next post SSIS Design patterns

      Hope you will like this post on SSIS Design Patterns.

      If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

    If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog :

    Link Resource Website

    For More information related to BI World visit my Mentalist Blog

    Link Resource Blog >> Daily Interesting links

    SQL Server Mentalist >> SQL Learning Blog

    Business Intelligence Mentalist >> BI World

    Connect With me on

    | FaceBook |Twitter | linkedIn| Google+ | WordPress | RSS |

    Leave a Reply

    Fill in your details below or click an icon to log in: Logo

    You are commenting using your account. Log Out /  Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out /  Change )

    Connecting to %s