I have test the connection for connections that do not use Windows Authentication and found 2 things:
1. Despite that fact that you are specifying the servername in the connection string, when you define the user name, you need to put in the machine name prior to the user name.
2. If fail to add in ";" after the password - then BIDS will fail to evalute the expression.
so as an example you connection string expression should look something like this if your server was called SQLDBSERVER.domain.network.org
"ServerName=\\\\" + @[User::ServerConnString] + ";Namespace=\\root\\cimv2;UseNtAuth=False;UserName=SQLDBSERVER\\MyUserName;Password=MyPassword;"
so the connection string used would be
If you are using local accounts for each machine, then perhaps you may want to think about storing the user name and passwords in SQL server (in the server list table), and pulling those in as extra variables..........
Personally, I would recommend going down the Windows Authentication route, and using a domain priviledged credential to run the job in sql server, rather than storing username & passwords with admin rights in sql server tables.
Let me know how you get on
MCITP: Business Intelligence Developer (2005)