File Name Expressions

A recent post on the SSIS Forums on MSDN reminded me how anti-intuitive it can sometimes be to work with SSIS expressions. For example, it's quite common (such as when using the File enumerator for the Foreach Loop container) to have a package variable that contains the fully-qualified file name of a file. But what if you need only a portion of the file name?

Well, the obvious answer (as I mentioned in an earlier post on expressions) is to look at the SQLIS.com Wiki for expression examples, because there are some good examples up there, including these first two. So here are a few more, each of which includes sample output for this sample input:

C:\Projects2008\RC0_SSIS_Test\RC0_SSIS_Test\FileExpressions.dtsx

Get the filename from a full file path:

RIGHT( @[User::FileName], FINDSTRING( REVERSE( @[User::FileName] ), "\\", 1 ) - 1 )

Output: FileExpressions.dtsx

Get the directory from a full file path:

SUBSTRING( @[User::FileName], 1, LEN( @[User::FileName] ) - FINDSTRING( REVERSE( @[User::FileName] ), "\\", 1 ) )

Output: C:\Projects2008\RC0_SSIS_Test\RC0_SSIS_Test

But what if your needs are covered there? Well, here are a few more examples:

Get the file extension from a full file path or file name:

RIGHT( @[User::FileName], FINDSTRING( REVERSE( @[User::FileName] ), ".", 1 ) - 1 )

Output: dtsx

Get the file name minus the extension from a full file path:

SUBSTRING (@[User::FileName], LEN( @[User::FileName] ) - FINDSTRING( REVERSE( @[User::FileName] ), "\\", 1) + 2,  LEN (RIGHT( @[User::FileName], FINDSTRING( REVERSE( @[User::FileName] ), "\\", 1 ) - 1 ) ) - FINDSTRING( REVERSE( @[User::FileName] ), ".", 1 )  )

Output: FileExpressions

Expressions are probably the single most powerful tool in the SSIS developer's toolset, so the more you use them the more you're likely to love them. But sometimes having a few good examples is the best way to get over the initial learning curve.