Standardising Large SSIS System
Standardising Large SSIS System
By : Kasim Wirama, MCDBA, MCITP
SSIS (SQL Server Integration Services) is ETL services and part of SQL Server database product. It fits from small system to large scale system. Usually large scale system means high complexity but it is not necessarily meant to. When you design and develop SSIS system, their complexities will proportional to the complexity of current system. Most likely you are not only design only 1 package in SSIS solution but it comprises of several SSIS packages and it is not unusual it spans across SSIS projects in one solution. And there is some order of execution among those packages, so I call them, SSIS package system, or in short SSIS system, instead of SSIS package. Standardising your SSIS system is much more important when they grow in complexity. With SSIS system standard, you will easier manage them and expansibility is possible in some other ways without adding complexity in maintenance. Here I would like to share important points to standard your SSIS system.
First thing you need to keep in mind for general rule of thumb is always creating configuration for connection manager and variables and assign expression on them as the way assign value indirectly instead of assign value directly to them.
Next point is that always the configuration file is re-used if they are shared among packages. So avoid creating a lot of logical servers.
Give logical name instead of physical name because physical name might be changed whereas logical name doesn’t.
Always use matching configuration names and variable names to let maintainer find the issue (in case it’s raised) more quickly.
If same variable is referenced by some SSIS packages, it is good to put it on root package and it will be recognized by descendant SSIS packages.
If there is layering between packages, it is advisable to name your package that reflects layering.
That’s simple tips for standardising SSIS system and it is necessary implemented on large scale SSIS projects.