Advise Required re Importing flat files 1

[shaunk]

HL7 is a formatting standard used in interfaces between Medical systems.It's generally a realtime system whereby messages are fed through a middleware server via TCP/IP and then discarded, or at least archived very quickly. The message is formatted according to an agreed standard with pipes and ^ etc seperating fields.
I want to use it in a different way. The messages will be output as a batch file, which my process will read and then update an SQL Server database accordingly.
I have written stored procedures to be used as a datsource for Crystal, but not much else in SQL Server.
What is the best way to go here. Can this be done in DTS or is oSQL a better option. Of course it will need to be scheduled, with logging etc. Is XML an option ?
Thanks

 

[DrAardvark]

Is your flat file always being added to? I am not sure what you are after but scheduling a job to import from flat files is fine. If the file is being added to you may need to copy/move it locally so it can be processed while more is added to the original.

DTS is the obvious choice I think because it will allow you to do VBScript etc. in it for moving files etc. It also allows logging to tables or files etc. Basically it is the most flexible thing available. You just need to ensure you have access to the file for long enough to copy/move it out since being medical I guess you don't want to lose any data!

 

[shaunk]

Thanks.
It will not be one single file. There maybe up to 1,000 files to process on a nightly basis i.e a file for each Order entered in the pathology system. I've been doing some reading on the FilesystemObject so there maybe some preprocessing/merging of the files into one file before parsing it.
The files are in fact a container for a bunch of results that are formatted with delimeters according to the result type.
Does VBscript have the equivalent of a Cobol redefine ie. if one type of result, the data will be formatted thus, for another type of message, it will be differently formatted.

 

[DrAardvark]

If all your files are of a certain naming convention or they are just ALL the text files in a directory for example there are many ways of getting SQL to loop until it has them all so don't bother with FSO for appending.

You need a loop, a staging table and a working directory.
Loop through the files, first it gets, into a staging table and move the file to "Working". Go hunting again (you can do an activex script on DTS operation which I can send you an example of. Once it runs out of files you can process the one table .. neat and tidy. At the end move all your "Working" files to "Processed" or delete as you like.

 

[shaunk]

The files will all be in the one location, all prefixed by "HL7".
Each file has to be processed one by one because I don't need every record/message in every file. I just need a subset of record/message types which I will then write to the one file.
So its Open file_A/Read File_A/examine message type/write file_B or Ignore.
I have obtained a book on DTS which I will have to read before I can put your "Loop/Staging table/Working directory" strategy into context.
In any event, can you send me the script. You have been very helpful. Thankyou.

 

[DrAardvark]

I still think that importing the whole file to a stage table is better but don't worry. It is just that you could copy the whole thing in and the processing you are talking about is then between the stage and stage2 for example table. All in SQL then so quicker

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()

Dim FSObj, fc, f, f1, s
Dim OBJPackage
Dim Response

Set fso = CreateObject("Scripting.FileSystemObject")

Set f = FSObj.GetFolder("X")
Set fc = f.Files
s=Null

For Each f1 in fc
Select Case something
Case HL7 maybe?
s = f1.name
Exit For
End Select
Next

If Not IsNull(s) Then

Set OBJPackage = DTSGlobalVariables.Parent

'Set previous step status to waiting.
OBJPackage.Steps("DTSStep_DTSDataPumpTask_1").ExecutionStatus = DTSStepExecStat_Waiting

'Do not execute task 2, step 1 will restart.
Main = DTSStepScriptResult_DontExecuteTask

End If

Main = DTSStepScriptResult_ExecuteTask

End Function