Getting Database name in expressions

• Anamika

  SSCertifiable

  Points: 6202

  More actions

  March 2, 2009 at 5:56 am

  #128108

  Hi,

  I want dynamically change the database name in my package.

  I code my connectionstring in the expression like this

  "Data Source=BLRSRV14\\INS01;User ID=SA;Initial Catalog=" + @[User::DBName] +"_DB;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;Application Name=SSIS-Packagewithnotify-{CA3413D4-3AAE-4858-A1D3-25832DB19AD1}blrwks.SSISClient.sa;"

  where the variable DBName holds the database name.

  but when i run the package i get the following error

  [Execute SQL Task] Error: Failed to acquire connection "blrwks.SSISClient.sa". Connection may not be configured correctly or you may not have the right permissions on this connection.

  [Connection manager "blrwks.SSISClient.sa"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.

  An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'SA'.".

  I got info from net that because of password i'm getting this error. so i changed my package protection level into 'EncryptSensitiveWithPassword'.

  then also i'm getting the same error. my package forgets the password. how to solve this

  any help, link is highly appreciated.

  Thanks,

  Regards,

  Viji

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  March 2, 2009 at 7:38 am

  #951863

  If you right click on your connection and select properties you will see an Expressions property in the property list.

  Click the ellipsis (...) on the right hand side of that and the property expressions window will pop up. On the first line,

  first column select the InitialCatalog property, then select the ellipsis (...) which will allow you to select the variable or

  enter an expression to set the property.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• Anamika

  SSCertifiable

  Points: 6202

  More actions

  March 2, 2009 at 7:58 pm

  #952200

  Hi,

  thanks for your reply.

  before doing it for connection string i tried it for initial catalog only.

  there also i'm getting the same problem. its because of SSIS forgetting the password.

  even i 'check' save password and protection level into 'EncryptSensitiveWithPassword'

  i'm getting the same problem.

  awaiting for a solution

  thanks,

  regards

  viji

• Anamika

  SSCertifiable

  Points: 6202

  More actions

  March 5, 2009 at 12:12 am

  #953817

  Hi Jack,

  Now its working fine with initial catalog itself. There was a difference between the database name and the name what i was getting into the variable.

  Because of that i was not able to establish the connection.

  Thanks,

  Regards,

  Viji