Posted by: Karlo Bartels | 2009/07/09

SSIS file/folder variables

Retrieving file and folder names from fully qualified file paths

Sounds easy enough, doesn’t it? Well, it took me some time to simply return the file name from a file path that was handed to me by a Foreach File Loop container in a SSIS package.

I mapped a variable called ImportFile to the container’s collection value, which contains a fully qualified file name, e.g. C:\SSIS\My Data\My File.txt. How would you extract the file or folder name from this variable? Let’s see how I did it:

  • Add a Derived Column Transformation component to your data flow and add columns which will hold the values for Folder and FileName 
  • Use the following expression to extract the file name for the FileName column:

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

  • Use this expression to extract the folder name for the Folder column:

REVERSE(SUBSTRING(REVERSE(@[User::ImportFile])
FINDSTRING(REVERSE(@[User::ImportFile]), "\\", 1) + 1, 230))

Indeed, SSIS is missing the LastIndexOf String function, which means I had to reverse the string to be able to find the final backslash in the path. After that, it’s just a matter of returning the part before or after the backslash and reversing everything again. I hope these expressions will save you some time during SSIS package development!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: