Friday, December 28, 2012

SSIS Expression to get filename from FilePath


Source : Beyond Relational

How to extract the file name from a file path where the number of sub folders or name of folder is not fixed. In simple terms you do not know the position where the file name starts.
Typical example: You have a foreach loop set to traverse sub folders and you set the retrieve file name to “fully qualified” and in parameter mapping you map this to a string variable strFilePath. You use this variable to set up the connection string for may be a flat file source(will not be discussing that). Now lets say you need to retrieve the file path for logging purpose or sending a mail. How do we do this??
Solution: Have another variable and name it as strFileName. Goto the properties of this variable by selecting the variable and clicking F4. Set the property EvaluateAsExpression to true. The Expression property becomes enabled. Put the below expression there:
REVERSE(SUBSTRING(REVERSE( @[User::strFilePath] ), 1, FINDSTRING(REVERSE( @[User::strFilePath] ),”\\”, 1)-1))
Lets say our file path is: C\Folder1\folder2\file.txt
Lets break this expression into parts -
REVERSE
(
 SUBSTRING
 (
  REVERSE( @[User::strFilePath] ),
        1,
        FINDSTRING
        (
   REVERSE(@[User::strFilePath] ),
            "\\",
            1
        ) -1
    )
)

we use the expression REVERSE(@[User::strFilePath]) it will reverse the string within the parenthesis in our case the file path. So the output of this segment would be:
txt.elif\2redlof\1redloF\C” Let us assume the output of this expression REVERSE(@[User::strFilePath]) = revPath for simplicity sake.
Now our work becomes simple just find the first instance of back slash and read the string from the first position to the first instance of a back slash.
Now what would be the expression to do the above: SUBSTRING(revPath, 1, FINDSTRING(revpath, “\\”,1)-1 )
If you notice after the Findstring I do a minus one that is done to remove the back slash else even that would be there in the output which is not required.
The output we get from the above expression is: ” txt.elif
I hope now things look simpler all we need to do now is do another REVERSE function call to reverse the above string to file.txt
UPDATE
Thanks to Mario Puskaric, a simpler expression for this is:
RIGHT(@[User::strFilePath],FINDSTRING(REVERSE(@[User::strFilePath]),"\\",1)-1)

No comments:

Post a Comment