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