How to use sql database table to configure ssis parameters and variables

SSIS and Import/Export
1

I would like to use a SQL Server table as a source for all connection manager values within . I would like to be able to easily switch which environments i am looking at based on an input value when the project opens. The reason i need to do this is because when creating our projects in dev I use different connections than when deploying them to prod, and i have hundreds of different connections being used. I don't want to have to switch anything in SSIS or in SQL Server Agent or the SSIS Catalog. I 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 [sql ConnectionString] , excel connection string , flat file connection string [InitialCatalog] , [UserName] , [UserName] , [Password] , source file path location , target path location etc. i would parameterize all connection managers that would have their values fed from this table. Please advise
2

The catalog has environments - you can defined the environment variables and just switch between each environment as needed. That is what they are designed for - so I don't understand why you would want to build something custom.

Also - it sounds like you are trying to create a configuration file. If so - then why not just use the ability within SSIS to use a configuration table?

2 Likes