Indirect VS Direct configuration in SSIS

Direct configuration

Pros:

·    Doesn't need an environment variables creation or maintenance

·    Scale well when multiple databases (e.g. TEST and Pre-Prod) are used on the same server

·    Changes can be made to the configurations files (.dtsconfig) when deployment is made using SSIS deployment utility

Cons:

·    Need to specify configuration the file that we want to use when the package is triggered with DTExec (/conf switch).

·    If multiple layers of packages are used (parent/child packages), need to transfer configured values from the parent to the child package using parent packages variables which can be tricky (if one parent variable is missing, the rest of the parent package configs (parameters) will not be transferred).

·    The two above cons can be bypassed by using SSIS deployment wizard, so if the configuration file switch (/conf) with DTExec is not used, packages need to be deployed via SSIS configuration wizard

Indirect configuration

Pros:

·    All packages can reference the configuration file(s) via environment variable

·    Packages can be deployed simply using copy/paste or xcopy, no need to mess with SSIS deployment utility

·    Packages or application is not dependent of configuration switches when triggered with DTExec utility (command line is much simpler)

·    Multiple layers (parent/child levels) scale better since all packages has all configuration values it needs to execute. They do not depend on parent packages and a child package can be used as a parent package without problems (no need to remove or add parent packages configurations

Cons:

·    Require environment variables to be created

·    Does not support easily multiple databases (e.g. TEST and Pre-Prod) to be used on the same server