Cannot extract Filename from Filepath using GetFileName in C#

Question

Cannot extract Filename from Filepath using GetFileName in C#

Weegee2017

SSC Eights!

Points: 994
More actions
April 27, 2020 at 11:38 am

#3746579

Hi there
I have a SSIS package with a connection manager as follows:
Package.Connections[Output_ExistingFile].Properties[ConnectionString]" ValueType=
\\acslonitstaging\Segmentation\PushPortfolio\Results\Existing_SVPP_XXXX_00001_00021_20200424_231245.csv
I want to extract the filename [Existing_SVPP_XXXX_00001_00021_20200424_231245.csv] in a C# Component
Ive tried the following
string fileName;
string path = Dts.Connections["Output_ExistingFile"].ConnectionString;
string result;
result = path.GetFileName(fileName);
MessageBox.Show(Message1);
However this is not working
Is there an alternative way to extract the filename from the filepath?

How ever

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16014 More actions · Answer 1

have a look at https://stackoverflow.com/questions/6921105/given-a-filesystem-path-is-there-a-shorter-way-to-extract-the-filename-without

from your code above "path" is not a System.IO object but rather a string - so you should even just be getting an error on the GetFileName.

once you look at the examples from the link above you will be able to fix your code

Weegee2017 SSC Eights! Points: 994 More actions · Answer 2

Thanks Frederico

Ok I got this working as follows:

string fileName;

string path = Dts.Connections["Output_ExistingFile"].ConnectionString.ToString();

fileName = System.IO.Path.GetFileName(path);

That gives me just the filename Existing_SVPP_XXXX_00001_00021_20200424_231245.csv

Thanks all for your all help on this

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

I'm not sure what the equivalent in the language of SSIS is but I have to believe that you shouldn't need to resort to a C# component. Here's how I do it using only T-SQL. There must be an equivalent in SSIS.

DECLARE @FullPath VARCHAR(500) = '\\acslonitstaging\Segmentation\PushPortfolio\Results\Existing_SVPP_XXXX_00001_00021_20200424_231245.csv'
;
 SELECT RIGHT(@FullPath,CHARINDEX('\',REVERSE(@FullPath))-1)
;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 4

Jeff Moden wrote:

I'm not sure what the equivalent in the language of SSIS is but I have to believe that you shouldn't need to resort to a C# component. Here's how I do it using only T-SQL. There must be an equivalent in SSIS.
DECLARE @FullPath VARCHAR(500) = '\\acslonitstaging\Segmentation\PushPortfolio\Results\Existing_SVPP_XXXX_00001_00021_20200424_231245.csv'
;
 SELECT RIGHT(@FullPath,CHARINDEX('\',REVERSE(@FullPath))-1)
;

This depends on what the script task is doing - and why the script task only needs the filename from the connection. There is no reason to make a connection to SQL Server just to parse the connection and return the filename.

It also depends on how the connection has been configured to get that filename. In most cases - a foreach loop container would be used to get the contents of a folder and if that is the case it can be configured to return just the filename portion instead of the full path...but that also depends on the process being implemented.

For example - I no longer use the foreach loop container to get the contents of a folder. I have a script task in C# that pulls the directory info and builds a recordset object - then a foreach loop container that loops over the recordset. The purpose for this configuration is to allow for customized sorting of the files - based on the datestamp of the filename and not the date/time stamps on the actual file. Because I control the data in the recordset - I can return only the filename or the full path or just a portion of the filename...

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

If you go back and read it, I wasn't suggesting going to SQL Server to do it. I said that there has to be a way in the language of SSIS to do the same.

Jeff Moden wrote:

I'm not sure what the equivalent in the language of SSIS is but I have to believe that you shouldn't need to resort to a C# component. Here's how I do it using only T-SQL. There must be an equivalent in SSIS.

It's also ironic that SSIS doesn't have something easier to build a record set of files like what you wrote a script task in C# for. That's why I don't even bother with SSIS for importing most files or doing any file handling in general.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 6

The equivalent in SSIS would be an SSIS expression - and yes you could use REPLACE, REVERSE and FINDSTRING in an expression. It all boils down to what the process flow is as to where it would be better to perform the task.

If you are already in a script task - then using an expression to parse the name would not be the better way. On the other hand - if you are in a data flow and need to parse a field/column in the pipeline - then an expression would be the better option.

And SSIS does have the ability to parse a directory for a list of files - that is the foreach loop container. You can set that to enumerate a list of files from a folder - and traverse subfolders...but it does not provide a way of sorting the list of files. The reason I now use a script task is that with a script task I can define the sort based on any criteria. If someone were to place the following files in a source directory:

Org_File_Claims_20200420.txt

Org_File_Claims_20200421.txt

I would need to process those files in the appropriate order - the 0420 file before the 0421 file. I cannot rely on the last write time - or created date/time because those can change based on how the files are copied or edited...

It can get even trickier - where the retro claims files were created after the standard claims files with later dates. In that case I need to process the retro file before the standard file and with a script task generating the recordset of files I control the sorting so they are processed in the correct order.

As for the connection itself - I generally define the connection string using a variable and expression on the connection. This allows for a package variable - or project parameter - the be defined with the root folder (used in the script task to get the list of files) and a file name variable (returned from the foreach loop recordset) and combined to generate the full file path. This makes it very simple to change the location of the source files without having to change any code.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Cool. Thanks for the info, Jeffrey. You should write an article on this stuff. I think it would be a big help to folks that use SSIS, especially people that need to do the kinds of things that you've described.

And, yes, I knew about the file loop but that's not quite the result set capability I was referring to.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)