Dynamic ConnectionString in SSIS / 使用动态 DB Connection

 

转自：http://p2p.wrox.com/sql-server-2005/63110-dynamic-connectionstring-ssis.html

 

Question:

 

Hi all,

   How to pass Dynamic ConnectionString Variables in SSIS Packages. I need to pass the ServerName, UserID,Password and Database Name through parameters like Global variables.

   Please help me,

Thanks in advance,

 

Answer :

devendar,

You can do this by using Expressions in the Properties of the Connection Object. There are lots of parts to this but I will give you the basic gist:

 

• Create your variables in the Variable tab, mine are ProjectServer (String), DBName (String)

• Create your DB Connection Object connecting directly to the actual Server and Database...Test your Connection
• Right Click on the Connection Object and Select Properties
• In the Properties Window Select Expressions by clicking the grey box with 3 dots ...
• Select ConnectionString from the dropdown in the 1st column, then click the grey box with 3 dots.
• In the Expression Editor that opens enter the following: "Data Source=" + @[User::ProjectServer] + ";Initial Catalog=FinancialProcessing;Integrated Security=True;"

.

This example will use the variable I have defined for ProjectServer and incorporate it to the ConnectionString. Your ConnectionString will be more complex and use each of the variables you create. There is much more you can do with variables but
this should get you started. Good luck.