Using configuration files without the package installation wizard

[Jerry Boggess]

I may be missing somthing here but it appears the only way to get
configuration information for a package into the SQL MSDB or FileSystem is to
use the Package Installation Wizard. When I use the wizrd I get prompted to
make changes to the configuration settings and then the dtsConfig files get
stored in a location the package checks during execution. I have went to the
dtsConfig directory and dynamically changed package information when needed.
If I use the SQL SSIS Import wizard it does not recognize the configuration
file is there and the same goes for using the "Save Copy As" feature in the
BI Studio. To make matters worse even if I do use the Package Installation
Wizard to move the pacakge into SQL I cannot see the configuration file being
use when I look at the configurations option in dtExecUI. Any additional
insight would be appreciated.

[Jerry Boggess]

I ended up reading quite a bit more about SSIS pacakges. Here are some
notes. For me, I decided to go with SQL Configurations because they worked
the best in my environment. The least attactive option was doing the SQL
Store pacakges using XML Configuration files since the configuration files
directory shows up under the FileSystem store and seems a little confusing
looking at it months down the road. Here are my observations:

DTSX Configuration File type
XML Configuration File
Pros
1)Easy to deploy to the SQL FileSystem or SQL Store. The two stores
combined are known as the SSIS Package Store.
2) You can use the Package Deployment Wizard or Copy/Paste if you are doing
a FileSystem store deployment. The default FileSystem package store
location, if doing copy/paste is C:\Program Files\Microsoft SQL
Server\90\DTS\Packages.

Cons
1) If you want to deploy to the SQL Store you need to make sure the explicit
path to any configuration file is set in the Packages DTSX file. If an
explicit path is not set the SQL Store will not see the dtsConfig file so it
will not be used. Taking the default location to create the dtsConfig
configuration file will not place a drive letter or UNC path in the package
itself so, again, unless the configuration and package are stored in the same
directory the package becomes unusable.
2) Anyone with access to the directory on the server can make the dtsConfig
configuration file unusable by the package.
3) If you have the projects CreateDeploymentUtility set to True you will get
a failure on the build if you attempt to use the same dtsConfig file by more
than one package.

Indirect XML Configuration File (Environment Variable)
Pros
1) Easy to change the package name and location of the dtsConfig file on
each server the package needs to be deployed to. This is because you
specify the name and location of the dtsConfig configuration file as the
environment variables value.
2) You can create a default configuration file to place server specific
information in, like server name and connection string, and have each package
use this one configuration file.

Cons
1) A little more setup time required since you need to create the
Environment variable on each server you may need to use the configuration
file on.
2) Anyone with access to the directory on the server, or with the ability to
alter environment variables can make the dtsConfig file unusable by the
package.

SQL Configuration
Pros
1) The packages and their configurations are stored in the same SQL instance
and have the security protection provided by SQL Server.

Cons
1) Additional knowledge of SQL Server may be needed to make adjustments to
the configurations within the tables.
2) Additional initial setup time would be required to come up with a plan to
keep ALL configurations in a central database.