meowsh
asked on
DTS - How to batch load a fixed width file with header and footer
Experts,
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
Meowsh
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
Meowsh
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Anthony Perkins
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.
ASKER
Acperkins,
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 :
8.0
1
1 SQLCHAR 0 609 "" 2 AllTheText SQL_latin1_General_CP1_CI_
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
Meowsh
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 :
8.0
1
1 SQLCHAR 0 609 "" 2 AllTheText SQL_latin1_General_CP1_CI_
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
Meowsh
Two questions:
1. What is your row delimiter?
2. How are you calling bcp?
1. What is your row delimiter?
2. How are you calling bcp?
I suspect you need to change this:
8.0
1
1 SQLCHAR 0 609 "" 2 AllTheText SQL_latin1_General_CP1_CI_
To:
8.0
1
1 SQLCHAR 0 609 "\r\n" 2 AllTheText SQL_latin1_General_CP1_CI_
8.0
1
1 SQLCHAR 0 609 "" 2 AllTheText SQL_latin1_General_CP1_CI_
To:
8.0
1
1 SQLCHAR 0 609 "\r\n" 2 AllTheText SQL_latin1_General_CP1_CI_