Solved

SSIS 2008R2 VB.NET Script:  How to get the file name from a full path

Posted on 2013-10-30
2
890 Views
Last Modified: 2016-02-11
Hi All

I have a ForEach File Loop where I'm assigning the full path of a file to variable @sSourceFile.  Good to go.

Question:  Using either VB.NET script or a Derived Column expression, how do you get the file name?

Example:  @SourceFile = "H:\go\here\then\go\there\foo.txt"
Desired value:  foo.txt
0
Comment
Question by:Jim Horn
2 Comments
 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 250 total points
ID: 39612672
SELECT SUBSTRING(@SourceFile, LEN(@SourceFile) - CHARINDEX('\', REVERSE(@SourceFile)) + 2, LEN(@SourceFile))
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 0 total points
ID: 39612697
Figured it out.

Up top in the declarations section...
Imports System.IO

Open in new window

... then in the main ...
Dim fi As System.IO.FileInfo = New FileInfo(Dts.Variables("sSourceFile").Value.ToString)
MsgBox("File name " + fi.Name)

Open in new window


Lowered points and awarded for the T-SQL answer, which may have been used if I couldn't come up with a VB.NET answer.

Thanks.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now