ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SSIS Expession Sample

SSIS Expession Sample

原创 Linux操作系统 作者:magicgao8888 时间:2009-04-09 09:45:43 0 删除 编辑
Expression Samples


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
Later see the
ExpressionDateFunctions page.


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14321372/viewspace-586815/,如需转载,请注明出处,否则将追究法律责任。

上一篇: FTP 命令大全
下一篇: 准备购机
请登录后发表评论 登录
全部评论

注册时间:2009-03-10

  • 博文量
    35
  • 访问量
    72313