I am currently writing a SQL 2000 DTS routine to batch load a fixed width flat file into staging table. Once 'staged' I will run a stored proc to load the data into the schema.
The fixed width file is quite wide, up to 609 characters.
The number of records in the file varies for each run.
It has both a header and footer. I want to store the header data in a 'load history' table. I can ignore the footer.
I'm imagining that what I need to do is
1. Use an ActiveX VBScript node to
(a) read the first line of the file (the header) and trap the header info in variables
(b) Using the variables, call a stored procedure (which determines if the file has already been loaded or not and saves the header record in a load history table)
(c) If the file has already been loaded, stop the load reporting failure
(d) If the file has NOT already been loaded, delete the header record (so that it doesnt get bulk loaded) and continue
2. Use an ActiveX VBScript node to delete the last line of the file (the footer)
3. Then use a bulk insert task to bulk insert the remaining data.
My questions are thus
1. Does my approach seem reasonable and in particular, is there a better way of handling the header.
2. Can you please provide sample VBScript to do steps 1 and 2?
Appreciate that this is a big question so have offered lots of points
Thanks in advance