Read Filename into a variable

Question

Read Filename into a variable

Ray Waldron

Old Hand

Points: 353
More actions
July 7, 2009 at 4:59 am

#134042

Hi all.
In SSIS I have a FOR EACH LOOP container that reads the file paths of all csv files contained in a folder.In the enumerator configuration I have to set "Fully Qualified" so that I can loop through each csv and insert into an SQL DB..This is stored as a variable "FILEJUSTFOUND"
My question is..Now I also want to insert the actual filename into the DB.I know I can use derived column task but I don't know how to read just the filename into a 2nd variable,If I use the current variable "FILEJUTFOUND" then the drived column contains the full path to the file and I need this variable for the for each look to work.
Can someone tell me how to add a 2nd variable to store the filename..Do I need an expression ?
Thx,
Ray..

Viewing 6 posts - 1 through 5 (of 5 total)

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

MSKR SSC-Addicted Points: 492 More actions · Answer 1

Hi Ray

Even i had similar requirement.It is possible u can use the variable which you have used in the for loop .That variable contains full file path with the file name so take a script task and read that varible

in script task use the fallowing code.

Public Sub Main()

Dim SourceFileName As String = Dts.Variables("FILEJUSTFOUND").Value.ToString()

Dim CharIndex As Integer = SourceFileName.LastIndexOf("\") + 1

Dim File_Name_Mod As String = SourceFileName.Substring(CharIndex)

Dts.Variables("File_Name_Out").Value = File_Name_Mod

Dts.TaskResult = Dts.Results.Success

End Sub

Now u ill get File_Name_Out as ur file name.and use executeSQL task to update the perticular column

Pls revert back if u face any problem

Cheers

MSK Reddy

Ray Waldron Old Hand Points: 353 More actions · Answer 2

Hiya and thx for replying.

I have added your code to a script task but how to I now use this new variable in a derived column task? I can only see the [User::FileWeJustFound] variable for use in the expression.

How do I now use your File_Name_Out variable?

thx again

Ray..

Phil Parkin SSC Guru Points: 246986 More actions · Answer 3

You need to create the new variable outside of the script task first - same as creating your other variable. Your script task just sets its value.

For the script task, you will also have to add FILEJUSTFOUND to the 'ReadOnlyVariables' list and File_Name_Out to the ReadWriteVariables list before the code will work.

Then your new variable should appear for you ...

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Ray Waldron Old Hand Points: 353 More actions · Answer 4

Thx guys I got it working but the filenames that get entered say file1.csv file2.csv file3.csv

How to i change Script code to remove the .csv and just leave file1,file2, etc.

Thx again for your help.

Ray..

Current code is

Public Sub Main()

Dim SourceFileName As String = Dts.Variables("FILEJUSTFOUND").Value.ToString()

Dim CharIndex As Integer = SourceFileName.LastIndexOf("\") + 1

Dim File_Name_Mod As String = SourceFileName.Substring(CharIndex)

Dts.Variables("File_Name_Out").Value = File_Name_Mod

Dts.TaskResult = Dts.Results.Success

End Sub

Ray Waldron Old Hand Points: 353 More actions · Answer 5

Hiya all,

I managed to just get the filename using the following code

Public Sub Main()

Dim SourceFileName As String = Dts.Variables("FileWeJustFound").Value.ToString()

Dim tmpInt(1) As Integer

tmpInt(0) = InStrRev(SourceFileName, ".")

tmpInt(1) = InStrRev(SourceFileName, "\", tmpInt(0)) + 1

'

If tmpInt(0) 0 And tmpInt(1) 1 Then

SourceFileName = Mid$(SourceFileName, tmpInt(1), tmpInt(0) - tmpInt(1))

Dts.Variables("File_Name_Out").Value = SourceFileName

Dts.TaskResult = Dts.Results.Success

End If