DTS - How to batch load a fixed width file with header and footer

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
Who is Participating?
Anthony PerkinsCommented:
This is the way I would recommend (and the way we currently do it).  Import the whole table into a staging table consisting of at least two columns:  An IDENTITY column (ID) and a data column (Data)  of width 609 bytes (or whatever is the maximum length).

Strip out of this staging table the Header record(s) into the one ('load history') table and the rest into another table(s) using SUBSTRING and CAST or CONVERT

To make your life easier, create a couple of VIEWS that match the layout and have a WHERE clause to filter out either the Header record(s) , Footer record(s) or the rest.
Anthony PerkinsCommented:
The big advantage of this approach is that you skip all the ActiveX baloney and you can validate the whole file prior to starting to extract to the final tables.
meowshAuthor Commented:

Thanks for your reply.
I hadn't thought of that approach but agree that it would alleviate the ActiveX complication.

I have been experimenting with the approach you suggest but I am having trouble getting the DTS bulk load task to work.  I think this is because the Header is 113 characters wide and the real data 609 characters.  

My table is :

CREATE TABLE [dbo].[imp_roe_staging1]      
      (PK            INT                        IDENTITY(1,1)
      ,AllTheText      VARCHAR(1000))

My current bcp.fmt file is :

1       SQLCHAR       0       609    ""      2     AllTheText    SQL_latin1_General_CP1_CI_AS

Regardless of whether I set the field width to 113 or 609 I dont get the data imported the way I want to.  For example, with it set to 609, BCP puts the 113 char header and 496 chars of the first data line into the first row of the table.  The second row of the table therefore starts at char 497 of the first data line and ends at 496 of line 2...etc.  If I set the field width to 113, I get row 1 as the header, row 2 as the first 113 chars of data line 1, row3 as the second 113 chars of data line 2 etceteras.

I think I must be missing something basic here!
Can you advise what the bcp.fmt file should look like?

Thanks in advance

Anthony PerkinsCommented:
Two questions:
1.  What is your row delimiter?
2. How are you calling bcp?
Anthony PerkinsCommented:
I suspect you need to change this:
1       SQLCHAR       0       609    ""      2     AllTheText    SQL_latin1_General_CP1_CI_AS

1       SQLCHAR       0       609    "\r\n"      2     AllTheText    SQL_latin1_General_CP1_CI_AS
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.