SQL Server Performance

dynamic connection

Discussion in 'SQL Server 2005 Integration Services' started by fmardani, Aug 6, 2007.

  1. fmardani New Member

    In the SSIS package, I have made the filename dynamic i.e. set as a variable.
    How is it possible to do the same thing for the database name in the oledb connection?
    I looked at the connectionString property for the OLEDB connection. ConnectionString looks long and the databasename is in this text.
    Not sure how to make this databasename inside the connectionstring dynamic?
  2. dineshasanka Moderator

    1. create variable and assign that variabel to connection manager
    2. Change variable from a script task
  3. laurie_csi New Member

    I'm not having any luck with this - could you please point me to an example?
  4. dineshasanka Moderator

    where you got stuck?
  5. laurie_csi New Member

    I created a variable that contains the name of my database. My connection manager connection string is set to Data Source=SQL01A;Initial Catalog=@User::WarehouseDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;
    When I try to access the connection manager during execution, I get the following error:Error: 0xC0202009 at Package, Connection manager "Dynamic Connection": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login failed for user 'SQL01Ahm3152L'.".
    An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot open database "@User::WarehouseDB" requested by the login. The login failed.".
    Error: 0xC00291EC at Populate Patient Information, Execute SQL Task: Failed to acquire connection "Dynamic Connection". Connection may not be configured correctly or you may not have the right permissions on this connection.
    Task failed: Populate Patient Information
  6. dineshasanka Moderator

    Define another variable called ConnectionString
    Then use a Scripttask and pass user variable to it.
    inside the script task, create connection string
    Set ConnectionString = "Source=SQL01A;Initial Catalog="+@User+"::WarehouseDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False"
    Then assign connection string of the connection manager to above variable
    One question, where is you password
  7. laurie_csi New Member

    I don't understand what I'm supposed to do - I tried what you said and it won't allow me to use the ConnectionString variable. It keeps telling me "Property is not valid." I don't understand why setting a value in a ScriptTask is any different than setting it anywhere else. Please bear in mind that I am a newbie with SSIS and have never done this before (though I've been programming in C, C++, and Java for years) - this is a completely foreign environment to me.
  8. satya Moderator

    Video for SSIS beginners, fyi.

Share This Page