Import Multi-Row Record Text File with DTS

I have a text file I need to import into a SQL Server table. Each record spans several lines. Does anyone have a vbscript routine that wil go thru this text file and put each record on one row? Once the records are one row, DTS will easily handle the import. Or is there a better way?

Sample multi-row records:

WRLDWYXCDS1 ALT101 APR04 21:30:24 6879 FAIL ALT
HOST 00 0 08 00 DN 3073477171 1st CYCLE
TEST TYPE CKTTST DIAGNOSTIC RESULT BIC/EBS LC TRBL:PTRN 000E S= 1 R= 0
ACTION REQUIRED Replace Card CARD TYPE 6X21AC

WRLDWYXCDS1 ALT101 APR04 22:31:37 7672 FAIL ALT
HOST 00 0 08 00 DN 3073477171 1st CYCLE
TEST TYPE CKTTST DIAGNOSTIC RESULT BIC/EBS LC TRBL:PTRN 000E S= 1 R= 0
ACTION REQUIRED Replace Card CARD TYPE 6X21ACDTS questions don't get a lot of answers on this forum. Perhaps most developers feel as I do, that DTS is a kluge solution of several different poorly integrated technologies. If you are lucky, my posting this reply will shoot your post back to the top of the list queue and somebody proficient in DTS will give you an answer.

Otherwise, I use DTS only for what it is best at; transfering and importing raw data. I avoid using DTS for transforming data, prefering instead to load the data into staging tables in my database and then run stored procedures to transfer it to the main database schema. I think one of the big advantages of this is that you separate the import process from the transform process, allowing you to flag and retain records that fail your business rules.|||Originally posted by eoffshore
I have a text file I need to import into a SQL Server table. Each record spans several lines. Does anyone have a vbscript routine that wil go thru this text file and put each record on one row? Once the records are one row, DTS will easily handle the import. Or is there a better way?

Sample multi-row records:

WRLDWYXCDS1 ALT101 APR04 21:30:24 6879 FAIL ALT
HOST 00 0 08 00 DN 3073477171 1st CYCLE
TEST TYPE CKTTST DIAGNOSTIC RESULT BIC/EBS LC TRBL:PTRN 000E S= 1 R= 0
ACTION REQUIRED Replace Card CARD TYPE 6X21AC

WRLDWYXCDS1 ALT101 APR04 22:31:37 7672 FAIL ALT
HOST 00 0 08 00 DN 3073477171 1st CYCLE
TEST TYPE CKTTST DIAGNOSTIC RESULT BIC/EBS LC TRBL:PTRN 000E S= 1 R= 0
ACTION REQUIRED Replace Card CARD TYPE 6X21AC

You don't need VB for this. It would be faster to create a staging table with one column. You want the column to be a char so you don't lose your fixed length. Have a select statement push it into a second staging table with char, but big enough for all four rows.

Then, you can either use a final parsing statement to divide it up, or export to a fixed length text file. It should be quicker to do it this way in sets then to parse through in VB where you'll have to parse through each line and length section.|||In your case it looks like you have records with five elements, where each element is a separate line in your raw data.

One solution to this is to import each record into a staging table as a single string of characters. A possible layout for your staging table would be:

RecordID (Identity)
GroupID (Int)
RecordType (Int)
RecordString (Varchar(500))
ImportErrors (Varchar(100))

As the data is imported into the staging table a RecordID is created to maintain the order.

Next you run a query(s) against your table to identify the RecordType for each record. For instance, maybe all the type 2 records start with 'HOST'

Lastly, group the records like this:

Update StagingTable
set GroupID = SubTable.GroupID
from StagingTable
inner join
(select StagingTable.RecordID, Min(GroupRecords.RecordID) GroupID
from StagingTable
inner join StagingTable GroupRecords
on StagingTable.RecordID >= GroupRecords.RecordID
and GroupRecords.RecordType = 1) SubTable
on StagingTable.RecordID = SubTable.RecordID

Now you have identified your imported records and their relationships to eachother, and you can load them into your schema. It's not neat or pretty, but it works. For each step you can flag problems by appending an error message to the ImportErrors field.