SSIS Standards
Staging Side:
• All Staging packages name should Prefix with Stage word.
• All Staging packages should suffix with _ET letter to better understand.
• All packages names should match with table name.
Data warehouse Side:
• All Loading packages should prefix with Load work.
• All Loading packages should suffix with _L letter to better understand.
• All packages names should match with dimension/fact/join/junk table name
Execute Staging packages package name:
• The name of the package which will execute all staging packages should be MasterStage.
Execute DW packages package name:
• The name of the package which will execute all loading packages should be MasterLoad.
Executing Process Cube package name:
• The name of the package which will process the cube should be MasterProcess.
Data Sources Name:
Connection manager’s name should be like this:
• Source Connection = Source
• Staging Connection = Staging
• DW Connection = Production
• Environment Setting/Connection = Config
Components Names:
• All components/objects at Source side, Transformation and Destination side should have appropriate names.
• Need to add annotations for each component and its process to understand the flow.
Variables:
All variables must be easily identified whether they are task based scope or package level scope e.g.
• L_ variablename local variable for some task/component scope.
• Pkg_variablename for package level scope.
Annotations:
There should be proper annotations for each Task and transformations. e.g.
Created By, Created on, Package Name, Modified date and Modified by etc.
Package Design:
Package should be design in such a manner so that we can handle the package for Incremental/Full Load.
Try to use less # or Blocking transformations or go with alternative solution of blocking transformations.
Categories the Transformation based on types: (Blocking / Semi blocking / Non blocking)
Each package should have different Package ID
Task/Package should be tracked by using loggings or SP or exception handlings (events)
Keep Packages as small as possible, Easier to understand, Easier to maintain and more flexible.
Use/Give the consistent naming convention.
Create one standard (template) package and use the same for all packages designing/creation
Formatting (Look and Feel):
All tasks should have same height and width
All Transformations should have same height and width
Each task/transformation should have appropriate name
All transformation flow lines should flow (left to right and top to bottom)
If task does not require for the time being then it should be disabled (not deleted).
Transformations cannot be disabled.
Configurational Settings:
Entire Solution should be in source safe for safety point of view (VSS, TFS ect…)
