A dump of all things SQL Server, SQL Reporting Services, Analysis Services, around Business Intelligence, Business Performance Management and technology.

Thursday, March 30, 2006

Using Expression Variables in SSIS

Here's a way to use Expression Variables in SQL Server Integration Services to dynamically create a connection string:

1. Create a new variable "SS_DST_Server"2. Create a new variable "SS_DST_Database"3. Create a new variable "SS_DST_Conn"4. Set 'Use as Expression' property on SS_DST_Conn to true.5. Under the expression, type in the connection string and append " + @[User::SS_DST_SERVER] + ";" where the server goes, and " + @[User::SS_DST_Database] + ";" where the database goes. Note it should start with =" and end with "