Monday, June 23, 2008

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.

4 comments:

Mettlus said...

the lenght-1 specified for function RIGHT is negative and is not valid. The lenght parameter must be position
Evaluate function right failed with error code 0xC00470E7

Mettlus said...
This comment has been removed by the author.
danny said...

It Helped me a lot... Thank you so very much

Brent said...

I was having the same issue as Mettlus. For some reason the expression syntax did not like the -1 value. To get around this I used the ABS function. Seems to work fine now. Example below:


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