SSIS - SSIS Configure connections
Author Nigel Rivett
This is to configure the connections so that they can easily be changed between environments
The package is configured via variables.
The connection strings are then set from the variables
The variables values can be set at run time or at design time.
They will generally be used to configure the package for the environment.
The environment can support a text file for the values which are used by the package loader.
I would advise always to set the connection string to an invalid value only available in the development environment.
This gives a check that the variables are being used to configure the package.
The variable names are associated with the destination type
For the staging database connection
I name these variables with the format a_Server_Staging, a_Database_Staging.
I use a_ to show that these are not work variables and appear at the top of the alphabetic list.
Set the values to your development server/database
When you create the database (adodb) connection use an expression to set the ServerName and InitialCatalog from these variables