passing connection string dynamically to ssis package

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

Answer 1

SQL Server 2008 connection string formats:

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

---

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

Answer 2

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

Answer 3

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

Answer 4

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 ?

Answer 5

I need your help to do the task at this post - http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/9f41bfff-03e7-4189-94f1-3e300448bff3

Answer 6

Hi etl man,

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

---

MH

Answer 7

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

Answer 8

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