Expressions are powerful in Integration Services. With Expressions you can dynamically set the servername (based on the environment) or the name of the excel file(if it is a fixed format). Let me give you an example.
I have a simple Dataflow task(as shown below) that gets the data from a table and exports it in a Tab delimited text file.
The Data Flow Task
I have set up a following variable called ServerName where I can change the value dynamically on the Connection String.
Variable Server Name
To configure the ConnectionString to use the Variable name dynamically at RunTime we need to configure Dimensions. This is how its done.Right Click on the Connection String and click properties(or click F4 by highlighting Databaes connection)
Database Connection Properties
In the Properties section click on the Expression for configuring nd also setting the Servername settings as below.
Expressions_Connection_Properties
Select ServerName
Configure Value as ServerName
Click Ok twice and the expressions are now configured. The best way to check it is to use the Bidshelper. (www.codeplex.com/BidsHelper) In the below diagram as you notice the Pink colour on the edge of the Connection String Icon which represents that the connection is configured via Expression.
The above example should give you a good example on how to configure Connection Strings using Expressions. Just keep commenting .
