Working With Multiple Flat Files

Question

Post reply

Working With Multiple Flat Files

cdun2

SSCertifiable

Points: 7893
More actions
November 4, 2006 at 3:52 am

#192998

Hello,
We have a system that, on a daily basis, produces a flat file of fixed width records from a mainframe. The files are placed in a single directory. The current date is included at the end of the file names in the format of mm|dd|yyyy.
I need to set up a package that will find the latest daily file, and import the contents of the file into a single table. Is it possible to somehow evauate the file names locate the most recent daily file, and then do the import? If not, would there be some way to determine the most recent file by reading the properties of the files to check for the creation date? What could I do about a case where a flat file didn't get created on a given day?
Thank you for your help!
CSDunn

Viewing 10 posts - 1 through 9 (of 9 total)

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

siva-321152 SSC Rookie Points: 31 More actions · Answer 1

siva-321152

SSC Rookie

Points: 31

November 4, 2006 at 4:51 am

#669961

Use ActiveX Scripting to read files

kaddie abdul SSC Eights! Points: 866 More actions · Answer 2

Hi cdun2,

TBH, the best way that I've seen this type of scenario handled is to build an AuditLog in your db for these files. The AuditLog tbl would contain a min. of the file name, and creation date (that date will probably match the mm/dd/yy of when the file was generated by the system and placed on the server, but it depends on when you decide to insert the record into the AuditLog). Every time a new file is generated by the system, the AuditLog is updated with that information.

Then you could build a DTS pkg that reads that table looking for the entry with the creation date that matches the datetime that you specify in your code (max(creationdate) prob. wouldn't work for your scenario since there are apparently occasions when a new file won't be generated). When the date values match, you could pass the file name as a variable to your pkg so that the code would import that specific file.

Does this make any sense?

cdun2 SSCertifiable Points: 7893 More actions · Answer 3

Yes, thanks for being so helpful. I'd also be iterested in seeing an Active X script that could do this kind of thing. SSIS appears to have some build in functionality in it for just this kind of situation, but that will have to wait.

My goal is to have this process totally automated, and eventually (early next year) have this working in SSIS.

Thanks again for your help!

CSDunn

cdun2 SSCertifiable Points: 7893 More actions · Answer 4

If you know of a documented example of this that I might be able to find on the web, could you point me to the URL? If not, and if you wouldn't mind, could you offer more detail on how I might accomplish this?

This is my first attempt at doing something like this (beyond basics) in DTS.

Thanks again for your help!

CSDunn

Greg Charles SSC-Forever Points: 45403 More actions · Answer 5

You can use xp_getfiledetails in an Execute SQL task to get the properties of a file. I don't have the details handy, but you can search this site for references.

Greg

Billy Hildebrand SSC Journeyman Points: 84 More actions · Answer 6

Here's an active x script file I use:

Function Main()
Dim tMain
tMain = DTSTaskExecResult_Success
  If isCurrentFile("FLATFILE.TXT") Then
    tMain = DTSTaskExecResult_Success
  Else
    tMain = DTSTaskExecResult_Failure
  End If
  Main = tMain
End FunctionFunction isCurrentFile(strPath, strFile)
    Dim fso
    Dim tFile
    Set fso = CreateObject("Scripting.FileSystemObject")
    If fso.FileExists(strPath & strFile) Then
  Set tFile = fso.GetFile(strPath & strFile)
  
  If formatDateTime(tFile.DateLastModified,2) = formatDateTime(Date,2)  Then
      Set tFile = Nothing
      Set fso = Nothing
      isCurrentFile = True
  Else
      Set tFile = Nothing
      Set fso = Nothing
      isCurrentFile = False
  End If
    Else
  Set tFile = Nothing
  Set fso = Nothing
  isCurrentFile = False
    End If
End Function