Dynamic connection manager using sql authentication in ssis

Question

I am trying to create a dynamic connection using Connection string. Required Details like (IP, DBname, username and password) are stored in database and I am fetching these details in from Execute SQL task and storing in variables. From these I am creating connecting string using expression. This works fine when I use Windows authentication with following connectionstring.

"Data Source="+ @[User::IP] +";Initial Catalog=" +@[User::DBN] +";Provider=SQLNCLI11;Integrated Security=SSPI;Auto Translate=False;"

However when I try to add username and password for SQL authentication SSIS is giving me error.

"Data Source="+ @[User::IP] +";User ID="+ @[User::USR]  +"Password= "+ @[User::PASS] +" ;Initial Catalog=" + @[User::DBN] + ";Provider=SQLNCLI11.0;"

Error:

[OLE DB Source [22]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager XXXXXXXXXXXXX failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Not sure what wrong. Can somebody help me what is wrong I am doing here.

Answer 1

First - you are missing semicolon before +"Password= " term and have redundant space at the end of this expression. You have to be precious with connection strings.
Second - as you are using dynamic Connection Manager, I would recommend to set task property DelayValidation=true, to validate your task right before it is executed, with correct connection string.

Answer 2

your connection string expression looks fine but it needs removing extra white spaces and adding a semicolon before password

"Data Source="+ @[User::IP] +";User ID="+ @[User::USR]  +";Password="+ @[User::PASS] +";Initial Catalog=" + @[User::DBN] + ";Provider=SQLNCLI11;Auto Translate=False;"

Answer 3

I actually got this to work as I was trying to do the same thing essentially. Same variables (except they are set as Project parameters) and used the corrections noted for the connection string format. I have a script task that sets an internal variable called "DBConnection" using string concat and the project parameters in the right places. Since the password is sensitive, don't forget to use GetSensitiveValue for it. Then on the connection manager properties, set the expression property connection string to the evaluated user variable DBConnection. Set delay validation on the task and if it is in a container, on the container as well.

Code in Script Task: Dts.Variables["User::DBConnection"].Value = "Data Source="+Dts.Variables["$Project::ServerName"].Value.ToString()+";User ID="+Dts.Variables["$Project::DBUserName"].Value.ToString()+";Password="+Dts.Variables["$Project::DBPassword"].GetSensitiveValue().ToString()+";Initial Catalog="+Dts.Variables["$Project::DBName"].Value.ToString() + ";Provider=SQLNCLI11.1;Auto Translate=False;";