How to store 'fully qualified' and 'name only' file name in SSIS variable

Question

I have an SSIS package that has a Foreach Loop container loading all .txt files in a static folder. I'm passing the fully qualified file name as a variable which I'm using in the connection string.

I now need to pass just the filename to a variable to use to execute a stored procedure, trouble is if I change the Foreach Loop collection to just retrieve the filename it prevents the rest from working.

Is there a way to pass both the fully qualified and name only file name to a variable in ssis?

Solution 1

Nope, you get to pick one: either fully qualified or file name only. Personally, if everything except the stored procedure expects fully qualified name, I'd create a second variable FileNameOnly and either populate it with an expression like

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

(this assumes you've stored the value in a variable called CurrentFileName)

Or you can use a script Task to assign the value to FileNameOnly and leverage the .NET System.IO.Path.GetFileName method.

Solution 2

You can add another variable @[User::Filename] and inside the Foreach loop container add an Expression Task with the following expression:

@[User::Filename] = TOKEN(@[User::FilePath],"\\", TOKENCOUNT(@[User::FilePath],"\\"))