Tuesday, December 28, 2010

SSIS Architecture Design Document

I'm reading: SSIS Architecture Design DocumentTweet this !
When I was quite junior in the IT industry, I use to wonder how one designs an architecture document. I used to collect templates of these documents from different sources. Today when I have matured enough to design one myself with my own sense and understanding of system, I feel that a basic idea of the sections that the SSIS architecture design document should contain would be of interest to all those minds who are growing up as future architects.

Delta Detection - This section would contain details about incremental load and Change Data Capture.

Extraction & Staging - This section would contain details regarding the treatment of extracted data and whether permanent / temporary staging are being used. If a staging area is used, it would require more elaboration about the same.

Facades - Interfaces and contracts are a vital part of any architecture design. This section would contain the views / SPs that you would create in your OLTP system. Even your CDC SPs can act as your facade. Also this section would describe whether you are using a push / pull model.

Trigger mechanism - Many solutions contain a application which triggers the ETL cycle. Whether you would use any scheduler like Autosys / SQL Agent should be described here.

Process Control Structure - Each category of packages classified by functionality should log which package was doing what and when. This would provide monitoring layer and control over the entire ETL execution.

Environment configuration - All details about environment variables, package configurations, and the storage locations of those configurations should be described in this section.

Tracability - Logging details should be mentioned in this section. Auditing can be of huge importance to support teams, so this section should be documented and designed keeping support teams in consideration.

Transaction Handling - Details about transaction handling within and across packages belongs to this section.

Error Handling - This section is very important to almost all teams screening your architecture document. Also is your error handling is weak, your transaction handling might suffer and this can be catastrophic to any kind of data loading.

Automation - Particularly in data warehousing projects, automation is one of the regular requirements. Trigger mechanism falls within the automation umbrella upto an extent.

Scalability - The more you loosely couple your package structure to take advantage of parallelism and more you design your logic keeping memory (synchronous and asynchronous transformation)in consideration, the more you gain in scalability. These details should be mentioned in this section.

Hardware Configuration - Server configuration details right from the number of cores till the amount of RAM that you intend to use belongs to this section.

Change Control & Deployment - Deployment methodology and location should be elaborated in this section. Though change control is a part of the configuration exercise, it's linked with how you would manage deployment. So it would make more sense to elaborate Change Control methodology in this section.

Feel free to add your comments to make this section more complete.


Anonymous said...

Hello Siddharth,

Thanks for your blog. I'm visiting it on a regular basis.
Your design document template is very usefull. Have you got an exemple of such completed document? I think a lot of people are interested.

Thanks a lot,

Siddharth Mehta said...

The templates that I have are not classified under individual assets, so I can't share them. Still I would try to create a public version, which I can share on this blog.

Anonymous said...

Hello Siddharth,

Congratulation for your recent MVP nomination and for your new mission at Avenade. I've worked with them in 2005 and they are "top" in BI.
BY chance, had you the chance to have a look on a public version of one template?


Siddharth Mehta said...

Thanks for the greetings. Honestly speaking I have been keeping quite busy these days and have not got chance to create the public version of the template.

But now as you have reminded me, this is on my radar now. You can just include the sections mentioned in the post and create a quick basic template out of it.

balaji hambeere said...

Please create public version.
Thanks for your blog its very helpful for us.

Omi Purohit said...

We all need it pls create

Omi Purohit said...

Good efforts , pls upload the template , we all need it :-)

Related Posts with Thumbnails