locked
passing connection string dynamically to ssis package RRS feed

  • Question

  • hi,

    i have create packages which loads the data from flat file to sql server table ,

    now i want to make my destination table connection dynamic what is format of connection string 

    i also need to pass user name and password for sql server dynamically

    in this case, what is the format for the connection string

    also  in package i used ADO.net  as source for  *.mdb files how i can set the commection to .mdb files dynamically which is used as source in my package

    • Edited by baba k Thursday, July 12, 2012 1:05 PM
    Thursday, July 12, 2012 12:35 PM

Answers

  • store the connection string in Configuration Table/File (right click on the package or http://msdn.microsoft.com/en-us/library/cc895212.aspx).

    there you can store the complete connection string including uid, pwd. once stored, when the package will run on server, it will always read the configuration table or file. you can change at any time the values of the connectionstring in that table or file,  without touching the package.

    regards

    joon

    • Proposed as answer by Eileen Zhao Sunday, July 15, 2012 1:30 AM
    • Marked as answer by Eileen Zhao Wednesday, July 18, 2012 6:14 AM
    Thursday, July 12, 2012 4:37 PM

All replies

  • SQL Server 2008 connection string formats:

    http://www.connectionstrings.com/sql-server-2008


    MCTS, MCITP - Please mark posts as answered where appropriate.

    Thursday, July 12, 2012 12:50 PM
  • Hello,

    To pass the connection string with user name and password. Follow this for Setting Expression

    if SQL Server 2008\R2

    Data Source=ServerName;User ID=UserName;Password=Password;Catalog=Databasename;Provider=SQLNCLI10;Auto Translate=False

    Data Source=ServerName;User ID=UserName;Password=Password;Catalog=Databasename;Provider=SQLNCLI.1;Auto Translate=False

    For Dynamic Table, Please Columns name and Data Type are same.  Reference http://msdn.microsoft.com/en-us/library/ms188439.aspx

    1. Create the variable of type string

    2. Add the table name into the variable

    3. Create your OLE DB Destination and select the variable that you have just chosen.

    Please let me know if in case you need any more information

    Thanks,

    Naveen T


    Naveen Kumar



    Thursday, July 12, 2012 12:56 PM
  • store the connection string in Configuration Table/File (right click on the package or http://msdn.microsoft.com/en-us/library/cc895212.aspx).

    there you can store the complete connection string including uid, pwd. once stored, when the package will run on server, it will always read the configuration table or file. you can change at any time the values of the connectionstring in that table or file,  without touching the package.

    regards

    joon

    • Proposed as answer by Eileen Zhao Sunday, July 15, 2012 1:30 AM
    • Marked as answer by Eileen Zhao Wednesday, July 18, 2012 6:14 AM
    Thursday, July 12, 2012 4:37 PM
  • Hello,

    To pass the connection string with user name and password. Follow this for Setting Expression

    if SQL Server 2008\R2

    Data Source=ServerName;User ID=UserName;Password=Password;Catalog=Databasename;Provider=SQLNCLI10;Auto Translate=False

    Data Source=ServerName;User ID=UserName;Password=Password;Catalog=Databasename;Provider=SQLNCLI.1;Auto Translate=False

    For Dynamic Table, Please Columns name and Data Type are same.  Reference http://msdn.microsoft.com/en-us/library/ms188439.aspx

    1. Create the variable of type string

    2. Add the table name into the variable

    3. Create your OLE DB Destination and select the variable that you have just chosen.

    Please let me know if in case you need any more information

    Thanks,

    Naveen T


    Naveen Kumar



    Hi Naveen,

    My databases don't really need a username and password. I login to a remote server. The remote server then allows me to access other remote database servers via 'windows authentication'. So I am not sure if my connection string needs a user name and password. Is that the case ?

    Saturday, May 11, 2013 6:55 AM
  • Hi etl man,

    If you have windows authentication you do not need to pass user name and password in the connection string.


    MH

    Monday, May 13, 2013 11:11 AM
  • Hi Naveen,

    I am trying to pass a connection string with dynamic  value through variable in ADO source control.

    but in SQL Server 2008\R2 there is no password filed in expression popup in connection manager.

    how can I pass connection string dynamically....

    please advice...

    Arunbabu.R

    Tuesday, December 10, 2013 9:19 AM
  • HI

    I used this connection string and evaluated it. Its working fine

    "Provider=SQLNCLI10.1;Data Source="+ @[User::SourceSQL] +";Integrated Security=SSPI;Initial Catalog="+ @[User::SourceDB] +";"

    Replace variable names with your variable names

    Tuesday, November 24, 2015 1:44 AM