locked
Dynamic connection string for OLEDB connection string in SSIS RRS feed

  • Question

  • Could you please suggest , how to write connection string dynamically for OLEDB connection string in SSIS

    I tired with the below expression which is failing

    “Data Source=”+ @[User::DemoSqlServer] +”;User ID=”+ @[User::DemoSqlUserName] +”;Provider=SQLNCLI10.1;Initial Catalog=” + @[User::DemoSqlDatabase] + “;Password=” + @[User::DemoSqlPassword]

    Again tried with 

    "Data Source="+  @[User::DemoSqlServer]  +  "; 
    Initial Catalog=MySamplesDB;
    Integrated Security=True;"

    but it is failing with the below error 

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

    Sunday, September 29, 2013 4:19 PM

Answers

  • Hi,

    Connection string using SQL Native Client 10 and SQL security should look like this:

    Provider=SQLNCLI10.1;Persist Security Info=False;User ID=sa;Password=Password;Initial Catalog=test;Data Source=COMMBOOK60\SQL2K8R2

    Connection string using SQL Native Client and SSPI should look like this:

    Provider=SQLNCLI10.1;Integrated Security=SSPI;Initial Catalog=test;Data Source=COMMBOOK60\SQL2K8R2

    Your first connection string expression is ok, but probably some of your variables are having wrong values. You can debug using breakpoints on tasks that use that connection manager. Use Watch list window to see the value of the connection manager expression.

    Your second connection string expression is missing Provider attribute.


    HTH, Regards, Dean Savović

    • Proposed as answer by Mike Yin Sunday, October 6, 2013 3:04 PM
    • Marked as answer by Mike Yin Monday, October 7, 2013 4:59 PM
    Sunday, September 29, 2013 7:48 PM
  • Hi SubhadipRoy,

    I you set the connection string of your OLEDB dynamically during the execution of the package, you should try setting the option "DelayValidation" to True on your connection manager.

    Cheers,

    Guillaume

    • Proposed as answer by Harry Bal Thursday, October 3, 2013 7:32 PM
    • Marked as answer by Mike Yin Monday, October 7, 2013 4:59 PM
    Monday, September 30, 2013 8:12 AM

All replies

  • The first connection string should work with an OLE DB connection.  Verify the variables are initialized properly by evaluating the expression at design time. 


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, September 29, 2013 5:32 PM
  • Hi,

    Connection string using SQL Native Client 10 and SQL security should look like this:

    Provider=SQLNCLI10.1;Persist Security Info=False;User ID=sa;Password=Password;Initial Catalog=test;Data Source=COMMBOOK60\SQL2K8R2

    Connection string using SQL Native Client and SSPI should look like this:

    Provider=SQLNCLI10.1;Integrated Security=SSPI;Initial Catalog=test;Data Source=COMMBOOK60\SQL2K8R2

    Your first connection string expression is ok, but probably some of your variables are having wrong values. You can debug using breakpoints on tasks that use that connection manager. Use Watch list window to see the value of the connection manager expression.

    Your second connection string expression is missing Provider attribute.


    HTH, Regards, Dean Savović

    • Proposed as answer by Mike Yin Sunday, October 6, 2013 3:04 PM
    • Marked as answer by Mike Yin Monday, October 7, 2013 4:59 PM
    Sunday, September 29, 2013 7:48 PM
  • This Error usually we get because either user name which you have given does not have permission to access the database or you might be giving wrong user name and password.

    Regards

    Naveen

    Monday, September 30, 2013 6:01 AM
  • Hi SubhadipRoy,

    I you set the connection string of your OLEDB dynamically during the execution of the package, you should try setting the option "DelayValidation" to True on your connection manager.

    Cheers,

    Guillaume

    • Proposed as answer by Harry Bal Thursday, October 3, 2013 7:32 PM
    • Marked as answer by Mike Yin Monday, October 7, 2013 4:59 PM
    Monday, September 30, 2013 8:12 AM