[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2005-04-22
Medium Priority
Last Modified: 2012-06-27
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
Question by:meowsh
  • 4
LVL 75

Accepted Solution

Anthony Perkins earned 1500 total points
ID: 13844937
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.
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13844955
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.

Author Comment

ID: 13866273

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

LVL 75

Expert Comment

by:Anthony Perkins
ID: 13866963
Two questions:
1.  What is your row delimiter?
2. How are you calling bcp?
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13867028
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question