Expression Language Patterns - SQL Server Integration Services Design Patterns - page 289

nection properties not in expressions, but rather as parameters, particularly when
you're dealing with structured data. Because of the sensitive and frequently changing
nature of connection metadata (server names, user names, and passwords), most ETL
professionals choose to externalize those settings to keep them stored securely and ex-
ternally to the package so they can be globally changed (rather than modified package
by package).
One recurring exception to this pattern is connections that interact with the file sys-
tem. There are several cases where using expressions helps to lighten the load of pro-
cessing file-based sources or destinations:
• When working with flat files, the files are to be named according to the
current date and/or time stamp (such as Medi-
care_2014_06_01.txt , for example).
• The files are expected to be filed in the file system according to the date
(such as D:\Data\2014\06\01\Medicare.txt ).
• A scheduled job loads a text file that always has the same file name, but
a copy of each day's file needs to be saved without overwriting the file
processed on the previous day.
For these cases, you can use a little dab of expression language to dynamically
build directory paths and file names that your connections in SSIS will use. For this ex-
ample, let's assume that you're generating a flat text file from within your package, and
you want to use a dynamic file name based on the current date. By setting the Con-
nectionString property from within the Properties window of the instance of the
Flat File Connection Manager, you can manipulate the runtime value of the file name.
As shown in Figure 10-2 , you're specifying the base file name and then appending the
elements of the current date to build a customized file name.
Figure 10-2 . Dynamic file name using expression