Use SQL Server database table as to configure SSIS project parameters and variables

Question

We would like to use a SQL Server table as a source for all connection manager values within SSIS. We would prefer to do this at the project level since many of our connections are already at the project level. We would like to be able to easily switch which environments we are looking at based on an input value when the project opens. The reason we need to do this is because when creating our projects in dev we use different connections than when deploying them to prod, and we have hundreds of different connections being used. We don't want to have to switch anything in SSIS or in SQL Server Agent or the SSIS Catalog. We would love for these to be strictly maintained within a SQL Server table.

1. How can we have these values fed from a SQL Server table into variables that feed the values or parameters? To reiterate, the end product would have 1 single table in SQL Server that contains all columns like [ConnectionString], [InitialCatalog], [UserName], [UserName], [Password], [HeaderRowsToSkip] etc. We would parameterize all connection managers that would have their values fed from this table. We need direction on how to accomplish this.

2. I would like to be able to contain both PROD and DEV in the same table with an [Environment] column that has a value of 'Dev' or 'Prod', and if possible we would like to have a prompt open when the Project opens in SSIS that asks which [Environment] we would like (A fillable prompt) that would in turn filter the results from the SQL Table to use either Dev or Prod based on what we enter.

If 2) is not possible, we would just use separate tables that could be switched in the connect manager manually based on when we are developing or deploying.

Answer 1

It sounds exactly the same as what "Environment Variables in SSIS Catalog" does!

The "Environment" in SSIS Catalog works like a configuration file for parameters, in your case, you can create Prod/Dev Environment, and map the variables in it to the Project Level parameters, which are mapped to Project Level Connection Managers.

Reference: Setup Environment Variables in SQL Server Integration Services Creating a Robust SSIS Development Environment using the SSIS Catalog

Answer 2

Apart from the discussion about how much securely is (a password inside a SQL Server Table, really?), I had made this kind of request using a special combination of PowerShell and in some cases getting info from Servers from SQL Table, for this, you can use a SQL Script Task Editor:

SELECT instanceName,databaseName,DataSource
FROM meta.InfoSSIS
WHERE environmentName = ? --Input parameter corresponding to DEV, INT or PROD

The output of this Query should be feeding the object, for instance, User: DatabaseList which is basically a DataType: Object and which store the info for SSIS instance, DB name, and DataSource. Internally you can have a ForEach Loop object which allows to interact with the DatabaseList object and work on it.