Create dynamic connection string with variables SSIS :

Create dynamic connection string with variables SSIS :

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 = ”SATISH”

VSQLDbName = ”TESTDB”
VSQLUserName= ”SAT123”

VSQLPassword = ”123”

(1) If integrated security 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;"

(2) 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;"