Retrieve Part of the Filename stored in a variable

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Retrieve Part of the Filename stored in a...

Retrieve Part of the Filename stored in a variable

Rate Topic

Display Mode

Topic Options

Author

Message

divyanth

Posted Friday, January 16, 2009 4:42 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, May 20, 2013 4:16 PM
Points: 215, Visits: 551

Hi

I am using a Foreach loop container and in the derived column im trying to retrieve only the Filename but when I use the variable which actualy stores the Filename I also get the path of the File For example If a use a variable in the derived column expression I get the following Output C:\Data\Loop\Filesame.xls but I only need the file name i.e., name... How do i do that .. I have tried using Name only option in the For each Enumerator but it gives me error asking me to check if the Worksheet Name [Excel Source [122]] Error: Opening a rowset for "New$" failed. Check that the object exists in the database.

Post #638589

ArkWare

Posted Tuesday, January 20, 2009 7:15 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 17, 2013 11:32 AM
Points: 40, Visits: 1,531

Hey Danny,

one way to get the file name only would be to create another variable. set it's property, EvaluateAsExpression to True. Then enter the following expression:

REVERSE(SUBSTRING( REVERSE(@[User::filename]),1, FINDSTRING( REVERSE(@[User::filename]) , "\\", 1 ) -1))

replace the @[User::filename] variable in the sample code with your variable that is being populated by the ForEachFile loop.

ArkWare

Post #639937

divyanth

Posted Tuesday, January 20, 2009 12:47 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, May 20, 2013 4:16 PM
Points: 215, Visits: 551

Thanks a lot dude!!!!

Post #640173

ArkWare

Posted Tuesday, January 20, 2009 1:09 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 17, 2013 11:32 AM
Points: 40, Visits: 1,531

No Problem, Dude

Expressions are your friend.. Cool

ArkWare

Post #640189

« Prev Topic | Next Topic »

Permissions