How to create dynamic connection string with variables SSIS

Share

Create Parameter on package level with string datatype with following Name, set default value with respect to your machine configuration I set according to mine

VServerName =”pc-aliraza”

VSQLUserName=”aliraza”

VSQLDbName =”Nwind”

VSQLPassword =”123”

If  integrated securtity with database is false or you connect with windows authentication following is the expression you have to set  expression at connection string property.

"Data Source=" + @[User::VServerName]  + ";Initial Catalog=" + @[User:: VSQLDbName]   + ";Provider=SQLNCLI10.1;Integrated Security=SSPI;"

If you want to connect with database with  Sql server authentication you have to use following expression string at  expression of connection string at connection in SSIS.

"Data Source="+ @[User::VServerName] +";User ID="+ @[User::VSQLUserName]  +"Password= "+ @[User::VSQLPassword] +" ;Initial Catalog=" + @[User::VSQLDbName] + ";Provider=SQLNCLI10.1;Persist Security Info=True;"

Tagged as: SSIS, SSIS connection string with expression, SSIS dynamic Connection string with variables, SSIS dynamic connection strings