How To Store 'fully Qualified' And 'name Only' File Name In Ssis Variable

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 c

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],"\\"))