CODEWITHSUNDEEP
dynamicssis
narendra gc
narendra gc

Reputation: 169

Dynamic connection manager using sql authentication in ssis

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.

Upvotes: 1

Views: 2516

Answers (3)

Elizabeth Polk
Elizabeth Polk

Reputation: 23

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

Upvotes: 0

Hadi
Hadi

Reputation: 37348

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

Upvotes: 0

Ferdipux
Ferdipux

Reputation: 5256

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.

Upvotes: 0

Related Questions