Passing connection string as variable in SSIS scripting

• ishaan99

  SSCertifiable

  Points: 6261

  More actions

  July 14, 2011 at 3:57 pm

  #244307

  I have a dtsx pckg which selects data from source which is sql server and copies to oracle env using vb scripting in ssis. now currently my pckg has the connection string as

  Provider=MSDAORA.Oracle;Data Source=<dbname>;User Id=<userid>;Password=<pwd>;

  to set up a connection to destination env. i am trying to have this pass as a variable to this script. is this possible? I tried to

  declare a string at pckg level saying Destconnection which has the entire string (Provider=MSDAORA.Oracle;Data Source=<dbname>;User Id=<userid>;Password=<pwd>;)

  and then use that as readonly variable in script property. somehow when using a

  MsgBox(Dts.Variables("DestConnection").ToString) i dont see the value getting passed onto script. am i doing something wrong or missing something? any help will be greatly appreciated. TIA

• Koen Verbeeck

  SSC Guru

  Points: 259115

  More actions

  July 15, 2011 at 12:20 am

  #1353208

  You're probably doing something wrong 🙂

  So you have a string variable at the package level which has the connectionstring as the value, right?

  Is it hard-coded, or loaded by an expression or package configuration?

  The script we are talking about, this is a script task right, not a script component (in the dataflow)?

  You did set the variable correctly in the script's editor?

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP