On Jul 27, 5:47 pm, DWalker <n...@none.com> wrote:
> (Re-Posted from another site with permission, since I have the same
> problem and he stated the issue well:) -- David Walker
>
> Kirk Shanks
>
> Uploading Text File with Header and Trailer Records
>
> I have bunch of records to upload that has trailer and header records
> from which I need to capture some information. They can also contain
> multiple batches within each record. The format is something like this:
>
> FHR file header record
> BHR batch header record
> DET detail record
> DET detail record
> DET detail record
> BTR batch trailer record
> BHR batch header record
> DET detail record
> DET detail record
> DET detail record
> BTR batch trailer record
> FTR file trailer record
>
> I need to capture information in the file and batch header records and
> add that to the detail records, but I have found that SQL Server does
> not (always) preserve the order of the records.
>
> Any suggestions?
>
> Kirk
>
> [Clarification:]
>
> I wish I could post an example, but all of my data is health insurance
> related and contains information that I cannot divulge publicly.
>
> The data is in a fixed width text file format. Each record starts with
> three letters (see above) that identifies it as either a detail record,
> file header, batch header, etc...
>
> The data I need is usually a date or batch number. So, I may have a
> batch header record like the following:
>
> BHR0001
> DET...
> DET...
> DET...
> BTR0001
>
> For each of the DET records between the batch header and batch trailer,
> I need to know that they are in batch 0001. There can be multiple
> batches.
>
> Kirk
>
> ----
>
> More info:
>
> I (David W.) have the same problem with my data files: In my case, the
> account number is in the batch header record only, and the detail
> records contain info about that account number. It's essentially the
> same problem. The data in the detail records needs to be combined with
> the account number and placed into a SQL table.
>
> This seems like a common scenario when importing data from legacy
> mainframe systems into SQL. Some people have claimed to me that SSIS
> can do anything natively. Can it import this kind of data easily?
>
> Thanks from both of us.

On Jul 28, 12:36 am, dan.r...@verizon.net wrote:
> On Jul 27, 5:47 pm, DWalker <n...@none.com> wrote:
>
>
>
>
>
> > (Re-Posted from another site with permission, since I have the same
> > problem and he stated the issue well:) -- David Walker
>
> > Kirk Shanks
>
> > Uploading Text File with Header and Trailer Records
>
> > I have bunch of records to upload that has trailer and header records
> > from which I need to capture some information. They can also contain
> > multiple batches within each record. The format is something like this:
>
> > FHR file header record
> > BHR batch header record
> > DET detail record
> > DET detail record
> > DET detail record
> > BTR batch trailer record
> > BHR batch header record
> > DET detail record
> > DET detail record
> > DET detail record
> > BTR batch trailer record
> > FTR file trailer record
>
> > I need to capture information in the file and batch header records and
> > add that to the detail records, but I have found that SQL Server does
> > not (always) preserve the order of the records.
>
> > Any suggestions?
>
> > Kirk
>
> > [Clarification:]
>
> > I wish I could post an example, but all of my data is health insurance
> > related and contains information that I cannot divulge publicly.
>
> > The data is in a fixed width text file format. Each record starts with
> > three letters (see above) that identifies it as either a detail record,
> > file header, batch header, etc...
>
> > The data I need is usually a date or batch number. So, I may have a
> > batch header record like the following:
>
> > BHR0001
> > DET...
> > DET...
> > DET...
> > BTR0001
>
> > For each of the DET records between the batch header and batch trailer,
> > I need to know that they are in batch 0001. There can be multiple
> > batches.
>
> > Kirk
>
> > ----
>
> > More info:
>
> > I (David W.) have the same problem with my data files: In my case, the
> > account number is in the batch header record only, and the detail
> > records contain info about that account number. It's essentially the
> > same problem. The data in the detail records needs to be combined with
> > the account number and placed into a SQL table.
>
> > This seems like a common scenario when importing data from legacy
> > mainframe systems into SQL. Some people have claimed to me that SSIS
> > can do anything natively. Can it import this kind of data easily?
>
> > Thanks from both of us.
>
> Based on my recent experience with loading large data files which may
> have some "interesting" processing problems, I would recommend that
> the data records be treated as a single column per record. I
> recommend that they be loaded into a table for temporary storage (a
> "permanent" table, as opposed to a #tablename temporary table), where
> the table has a single column wide enough to contain the largest
> external row (VARCHAR(200) or something like that, maybe), and an
> additional column like SEQNO declared as INT IDENTITY(1,1). This
> additional column will enable you to know the order of the original
> data in the external file.
>
> Once the data are in this temporary storage table, you can use fancy
> "set based" queries, or even cursors (much slower, but perhaps
> necessary) to process your data, writing the data to whatever
> combination of tables suits your purposes. I think you will find such
> cursors acting on data in tables to be much faster in operation than a
> Script task on data input.
>
> For the tasks you two describe, with the BHR and BTR rows showing a
> batch number that applies to the intervening records, you should be
> able to avoid any need for using a CURSOR.
>
> Into what sort of table would you be placing the Detail records?
> Something like shown here, perhaps?
>
> create table detail_data
> (
> batch_id varchar(20) not null,
> detail_row varchar(1000) not null
> )
>
> Or, maybe you want to extract certain columns/fields from each
> detail_row.
>
> In either case you could make a temporary table, e.g.,
> #batch_start_end, that lists the SEQNO for the start and end of each
> batch, and for all DETAIL records between these start and end row
> numbers, you would put the Batch number, and the Detail data, into
> your DETAIL table.
>
> create table #batch_start_end
> (
> batch_id varchar(20) not null,
> start_seqno int not null,
> end_seqno int not null
> )
>
> insert into #batch_start_end
> (
> batch_id,
> start_seqno
> )
> select
> substring(data_row, 4, 20),
> SEQNO
> from data_input_table
> where data_row like 'BHR%'
>
> update bse
> set end_seqno = tmp.seqno
> from #batch_start_end bse
> inner join
> (
> select
> substring(data_row,4,20) batch_id,
> SEQNO
> from data_input_table
> where data_row like 'BTR%'
> ) tmp
> on bse.batch_id = tmp.batch_id
>
> Does that make sense? Are more specifics needed than this "game
> plan"?
>
> Dan- Hide quoted text -
>
> - Show quoted text -