Ali Raza Zaidi

A practitioner’s musings on Dynamics 365 Finance and Operations

SSIS dynamic Connection string with variables

How to create dynamic connection string with variables SSIS

by

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;"