dynamic connection to oracle

• sqlstar2011

  SSCertifiable

  Points: 6000

  More actions

  May 6, 2009 at 2:15 pm

  #133788

  I would like to point the connection manager at run time to a specific oracle database. Seems like it can be done using Connection Manager's Expression Property pane but I am not sure how. To access oracle I have the password stored in a variable.

  Can someone tell me how to setup the Connection Manager's Expression Property?

  according to this post:

  http://www.sqlservercentral.com/Forums/Topic705191-148-1.aspx

  I can do it like this:

  Data Source=SERVICE NAME;User ID=Oracle Username;Provider=MSDAORA.1;Persist Security Info=True;

  obviously one above is missing password... anyhow here is what I did in expressions property for CONNECTION_STRING

  Data Source=cost3;User ID=userABC;Provider=MSDAORA.1;Password=password;

  but I get an error message:

  Attempt to parse the expression "Data Source=cost3;User ID=userABC;Provider=MSDAORA.1;Password=password;" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

  what am I doing wrong? or if someone can tell me more about how to set this thing up.. thanks

• Mukti

  SSCrazy

  Points: 2826

  More actions

  May 6, 2009 at 3:16 pm

  #990011

  Put you string in double quotes inside the expression builder

  "Data Source=cost3;User ID=userABC;Provider=MSDAORA.1;Password=password;"

  HTH

  ~Mukti