How do I extract file name from a path within an SSIS package?

2
votes

I would like to know how to extract the file name using SSIS from the below given paths:

Paths:

O:\SourceFiles\Primary\care\ALC121.csv
O:\SourceFiles\Primary\care\COPL119.csv

I would like to insert the names and the full path into a table as shown below:

Table data:

QueryName  FileName 
---------  ---------------------------------------
ALC121     O:\SourceFiles\Primary\care\ALC121.csv
COPL119    O:\SourceFiles\Primary\care\COPL119.csv
3
ssis

3 Answers

4
votes

I'm assuming that the full file names are coming in off the ForEach Enumerator for files. It seems the request then is to reduce that string down to just the base file name. Some folks will use expressions to get you there but I find that an ugly approach. Instead, I'd use a script task and take advantage of the functionality in the .NET library.

System.IO.Path.GetFileNameWithoutExtension

Returns the file name of the specified path string without the extension.

Create a second variable, call it QueryName with a type of String and set it as a read/write variable in the script. Assign the value of it based on the results of that metchod call with the FileName value.

Then use an Execute SQL Task to insert them into your table. Use a ? as a parameter.

1
votes

Within a derived column, you can use TOKEN() and TOKENCOUNT() functions to do that:

TOKEN(
     TOKEN(@[User::Filepath],"\\", TOKENCOUNT(@[User::Filepath],"\\"))
, ".", 1)
0
votes

File name without extension : 

select substring(?,len(?)+1,(charindex('.',?)-1)-LEN(?))

for 4 parameter mapping
0 - FullPath
1 - FilePath
2 - FullPath
3 - FilePath