July 7, 2009 at 4:59 am
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..
July 7, 2009 at 6:41 am
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
July 7, 2009 at 10:50 am
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..
July 7, 2009 at 8:32 pm
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 9, 2009 at 5:06 am
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
July 9, 2009 at 5:55 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply