SSIS & dynamic environment + user variables

Setting Up User Variables

first of all create a table to hold user defined variables

CREATE TABLE [SSIS].[Variable]
(
    [ConfigurationFilter] [nvarchar](400) NOT NULL,
    [ConfiguredValue] [nvarchar](255) NULL,
    [PackagePath] [nvarchar](255) NOT NULL,
    [ConfiguredValueType] [nvarchar](20) NOT NULL
)  

populate the table with values accordingly

Setting Up Environment Variables

  To set up this approach at design time, follow these steps:

1. Before opening the Business Intelligence Developer Studio (BIDS) environment, create a system environment variable named SSIS_ROOT to hold the connection string for the SQL Server database. Enter the connection string into the value for the variable. Directions for creating a system environment variable can be found here: (http://technet2.microsoft.com/windowsserver/en/library/4029b464-c281-49af-84d5-6cd092e61a651033.mspx?mfr=true). This needs to be done before opening BIDS, since it caches the environment variables when it is first opened. It should also be created as a system environment variable, so that all users of the computer will have access to it.

From visual studio right click on the control flow tab and select package configurations