SSIS Tip #2: Dynamic Property Expressions
When creating an SSIS package, keep mind the power of creating dynamic Property Expressions. Any Read/Write property of a package, task, event, manager, etc can be manipulated at runtime via Property Expressions to change the behavior of a deployed, otherwise static SSIS package. This gives the developer a powerful means of adjusting the package on the fly.
For example, you can create an expression to set a property value to a User variable, which is then configurable post-deployment (Tip #1: How to do this). Using this method would allow you to set the connection string to a desired target after deployment, without creating separate packages (Production, Staging, Test, etc).
User and System variables are all available for use in your Property Expressions, which are written via the Microsoft SQL Server 2005 Integration Services expression language. The syntax is uncomfortable at first, but it may grow on you. Eh, probably not...it's kind of nasty.
Below is an example of a Property Expression I've used to create a unique flat file name based on A) User variables to define the root path and base file name, and B) the current DateTime. Whenever my package runs, a unique filename is produced. Most of the expression is parsing out pieces of the date into our desired format:
@[User::DestinationPath] + "\\" + @[User::RootFileName] + (DT_WSTR,4)YEAR(GETDATE()) + RIGHT("0" + (DT_WSTR,2)MONTH(GETUTCDATE()), 2) + RIGHT("0" + (DT_WSTR,2)DAY( GETUTCDATE()), 2) + RIGHT("0" + (DT_WSTR,2)DATEPART("hh", GETUTCDATE()), 2) + RIGHT("0" + (DT_WSTR,2)DATEPART("mi", GETUTCDATE()), 2) + RIGHT("0" + (DT_WSTR,2)DATEPART("ss", GETUTCDATE()), 2) + ".txt"
At runtime, the expression evaluates into "C:\MyPath\MyRootValue_20070412070021.txt". Set this expression as the ConnectionString property value of a Flat File Connection Manager, and you're in business.