September 10, 2008 at 9:13 pm
Hi,
I have few .csv files in a folder. The file names are in the form of YYYYMM (i.e., 200809 , 200808, 200807...). A new file is created at the beginning of each month.
I need to create a SSIS package that determines the most recent file in the folder and load that plus the previous 11 files data into the SQL.
Which means I need to load the data from most recent 12 files from the folder using the file names.
can someone help me with this....
September 10, 2008 at 10:16 pm
Getting the 12 most recent files in a folder is possible in a script task for sure. There is also a file system task which may be able to do it. Not sure. Never used it.
September 10, 2008 at 11:32 pm
Use script task and extract the date of creation property of the file, with the help of that you can extract 12 recent files from the folder.
September 11, 2008 at 10:29 am
The data is updated every week. This is not going to happen for only the last file but for all the files. Means, the data is getting refreshed for all the files. So, instead of taking the creation date, i need to take the name of the files like 200809 for the last one and than for 11 files brforethat like 200808, 200807.....
Can anyone help withthe code...
September 11, 2008 at 3:31 pm
This should get you started. Use a script task. there's alot of different ways to do this but here's the method I used. You'll probably have to modify the code to your liking. I had a similiar project where I had to do this some months ago. I had to get the last two files in a directory.
I started by creating a datatable with two colums. I then used a system.IO.DirectoryInfo to create a collection of the directory. I then loop through the directory adding the file name and also using a stringbuilder to build a string which represents the date (from the name of the file). Prior to inserting in the datatable I convert the string I just built to a date. You can also substitute this with the substring method if you know exactly where the date will be in the file name.
After the datatable is built with the file name and date, I can now add the datatable into a dataview which allows you to sort it descending. After sorted just loop through the view 12 times to get the last 12 dates.
For what it sounds like for you, you might want employ a similiar method to mine but instead used a Script Component a source to build the last 12 files and a SSIS recordset destination. You can then loop through the recordset destination loading each file. In my case I wasn't loading any data, just getting the last 2 files and doing stuff with. You need to load your data so using a recordset may work better for you. In any case see my code below which may get you started and you can branch off if you need to.
See code below:
Public Sub Main()
Dim FileDate As Date
'Create datatable for sorting
Dim dt As DataTable = New DataTable
dt.Columns.Add("FileName", GetType(System.String))
dt.Columns.Add("FileDate", GetType(System.DateTime))
Try
Dim Directory As String = Dts.Variables("DestinationDirectory").Value
Dim arrDirectory As DirectoryInfo = New DirectoryInfo(Directory)
For Each FileName As FileInfo In arrDirectory.GetFiles()
Dim sb As StringBuilder = New StringBuilder(FileName.Name)
Dim StartingPos As Integer = sb.ToString.LastIndexOf("n")
sb.Remove(0, StartingPos + 1)
sb.Remove(8, 4)
sb.Insert(2, "/")
sb.Insert(5, "/")
Try
FileDate = DateTime.Parse(sb.ToString)
'add row to datatable
Dim dr As DataRow = dt.NewRow
dr.Item("FileName") = FileName.FullName
dr.Item("FileDate") = FileDate
dt.Rows.Add(dr)
Catch ex As FormatException
'Row not added
End Try
Next FileName
Dim dv As DataView = New DataView(dt)
dv.Sort = "FileDate DESC"
Dts.Variables("CurrentFileToProcess").Value = dv(0)(0) 'Current file
'set package last file variable
Dts.Variables("LastFileProcessed").Value = dv(1)(0)
'Set holding directory
Dts.Variables("TempFileToProcess").Value = Directory & "temp_" & FileDate.ToString("MMddyyyy") & ".txt"
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
'fail package
Dts.TaskResult = Dts.Results.Failure
End Try
Thanks,
Strick
September 11, 2008 at 4:24 pm
use script task to copy latest 12 files to a folder & use for loop to import data
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply