Ssis: Configuration Of Dynamic Connection String Integrated Security Mode

Setup In the local environments, we're using SQL Authentication with Username and Password to connect to the databases. I created a Project Connection Manager that has expressions

Solution 1:

You need to handle situation when in one environment you have to use SQL Authentication, and on the other - AD Authentication. This can be done with help of SSIS Catalog Environment variables. When you create a Project file, Visual Studio automatically creates the following so called project connection parameters for each OLEDB connection manager :

• CM.< conn manager name >.ConnectionString
• CM.< conn manager name >.InitialCatalog
• CM.< conn manager name >.Password Created as sensitive param
• CM.< conn manager name >.ServerName
• CM.< conn manager name >.UserName

OLEDB is an example, SSIS creates similar parameters for other connection manager types. Important fact, you do not have to create additional project parameters. The parameters mentioned are created on project being built and are present on all projects.

We create environment variables which specify connection string, DB name (initial catalog), Server Name etc. Good thing - Connection string variable is applied first, and then amended with the other variables. More details on these parameters is in MS Docs.

In case similar to yours, in Dev environment - using SQL Auth define Conn string for SQL Auth and specify username and password in corresponding variables. In QA env where SSPI is used - the Connection string is reworked for SSPI, UserName and Password environment variables are empty.