Expression Samples

Summary Sample expressions. If you want to find an expression someone has already built, or you have a useful expression to share, this is the place.

File and Folder Expressions

For the following samples the variable @[User::FileName] has a value of

 C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DtsDebugHost.exe

The expression for each example is shown, followed by the result. For file name expressions that use dates see ExpressionDateFunctions

Get the filename from a full file path

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

 DtsDebughost.exe

Get the directory from a full file path

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

 C:\Program Files\Microsoft SQL Server\90\DTS\Binn

Get the file extension from a path or filename

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

exe

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 )  )

 DtsDebugHost

Dynamic Table Name

Often when setting a the SQL statement for a source adapter you wish to have a dynamic table name. Since this cannot be parameterised using the standard parameters functionality built into OLE-DB, you need to use an expression. For the OLE-DB Source you would use a variable to handle the SQL Statement, and set the access mode to SQL command from variable.

So assuming we have variables for table name, and for this example we also have a data as well, now create your variable to hold the SQL, and set the EvaluateAsExpression property to true. The expression for the variable is shown below.

 "SELECT Column FROM " + @[User::TableName] + "
 WHERE DateFilterColumn = '" +
 (DT_WSTR,4)YEAR(@[User::DateTimeVar])
    + RIGHT("0" + (DT_WSTR,2)MONTH(@[User::DateTimeVar]), 2)
    + RIGHT("0" + (DT_WSTR,2)DAY(@[User::DateTimeVar]), 2)
    + "'"

 SELECT Column FROM MyTable
 WHERE DateFilterColumn = '20060915'

Date Expressions

See the ExpressionDateFunctions page.

SeeAlso Expressions, ExpressionDateFunctions