SSIS Package Configuration

Question

I have a package that loads data from a text file into a table but I am having trouble setting the package configurations. I was hoping that I could import my package to any database and that that package would automatically lookup the SSISConfigurations table on the database that package is installed on. The package seems to always look on the original database SSISConfigration table where I first created the package. Is it possible to tell the package to look on the source(where the package is installed) database for the SSISConfigration table? Note. I have created the package configuration to look at a SSISConfigruation table for the connection string.

Answer 1

You have to set the connection string to the connection used for the configuration database to match the machine you wish to use for configuration. You could set up your connection string to look something like this:

Data Source=.;Initial Catalog=ConfigurationDB;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;

The "." after Data Source= is a shortcut meaning the current server. As long as all of the servers you are running your package on have the same configuration database then this will work for you. Alternatively, you can set the connection string for your configuration database from an environment variable. In this case you can use whatever DB you want for each server in your environment. You will simply need to set the environment variable you use to have the proper connection string for that server.

Answer 2

We start with an environment variable that defines the database to be looking into. This varaiable is then set on every server (As well as the devs machines) to look to the correct database for that server. Then we have a configuration that uses the SSIS config tables for the rest of the configuration.