August 8, 2008 at 7:48 am
Below is the structure of the flat file. I have to load into SQL using either Bulk Insert or Dataflow in SSIS. Here are several issues here---
1. Header, Detail and Footer size is different
2. Uneven Columns in each row.
3. Particular 3 columns in details have sign charter infront of numeric value, so when we load into sql as decimal how we load sign ?
All the help will be really appreciated. ALso how to approach it?
I can think of doing conditional split based on H, D or T. But lengths are different for each records type. there are "," delimters, and i think records will have uneven columns, plus on 3 occasions where data is numeric type have sign char in front of it which is not delimited by any sign.
Thanks in advancee
RC70/71 RECORD LAYOUTS
01 ENCT-HDR-RECORD. 32 bytes
05 ENCT-HDR-REC-CD PIC X(01) VALUE ‘H’.
05 FILLER-1 PIC X(01) VALUE ‘,’.
05 ENCT-HDR-MC-PROV-ID PIC X(08).
05 FILLER-2 PIC X(01) VALUE ‘,’.
05 ENCT-HDR-BATCH-NUM PIC X(16).
05 FILLER-3 PIC X(01) VALUE ‘,’.
05 ENCT-HDR-BATCH-ID PIC X(04).
88 ENCT-HDR-BATCH-837I VALUE ‘837I’.
88 ENCT-HDR-BATCH-837P VALUE ‘837P’.
88 ENCT-HDR-BATCH-DRUG VALUE ‘DRUG’.
Detail
01 ENCT-DTL-RECORD. 279 bytes
05 ENCT-DTL-REC-CD PIC X(01) VALUE ‘D’.
05 FILLER-1 PIC X(01) VALUE ‘,’.
05 ENCT-HDR-MC-PROV-ID PIC X(08).
05 FILLER-2 PIC X(01) VALUE ‘,’.
05 ENCT-XCN-NUM PIC X(23).
05 FILLER-3 PIC X(01) VALUE ‘,’.
05 ENCT-TCN-NUM PIC 9(17).
05 FILLER-4 PIC X(01) VALUE ‘,’.
05 ENCT-HDR-SUBMITTER-ID PIC X(16).
05 FILLER-5 PIC X(01) VALUE ‘,’.
05 ENCT-HDR-STAT-CD PIC X(01).
05 FILLER-6 PIC X(01) VALUE ‘,’.
05 ENCT-HDR-TY-CD PIC X(01).
05 FILLER-7 PIC X(01) VALUE ‘,’.
05 ENCT-HDR-TXN-CD PIC X(01).
05 FILLER-8 PIC X(01) VALUE ‘,’.
05 ENCT-ALT-ID PIC X(14).
05 FILLER-9 PIC X(01) VALUE ‘,’.
05 ENCT-PROV-ID-IND PIC X(01).
05 FILLER-10 PIC X(01) VALUE ‘,’.
05 ENCT-PROV-ID PIC X(15).
05 FILLER-11 PIC X(01) VALUE ‘,’.
05 ENCT-BLNG-PROV-ID PIC X(08).
05 FILLER-12 PIC X(01) VALUE ‘,’.
05 ENCT-BLNG-PROV-TXNMY PIC X(10).
05 FILLER-13 PIC X(01) VALUE ‘,’.
05 ENCT-BLNG-PROV-ZIP-CD PIC X(05).
05 FILLER-14 PIC X(01) VALUE ‘,’.
05 ENCT-MCO-TCN-DAT PIC X(20).
05 FILLER-15 PIC X(01) VALUE ‘,’.
05 ENCT-TOT-REIMB-AMT-SGN PIC X(01).
05 ENCT-TOT-REIMB-AMT PIC 9(09)V99.
05 FILLER-16 PIC X(01) VALUE ‘,’.
05 ENCT-MC-ENCT-PD-AMT-SGN PIC X(01).
05 ENCT-MC-ENCT-PD-AMT PIC 9(09)V99.
MCO/CSP SYSTEMS MANUAL NEW MEXICO MMIS
ERROR REPORTS MAY, 2008 PAGE 118
05 FILLER-17 PIC X(01) VALUE ‘,’.
05 ENCT-ADJUD-DT PIC X(10).
05 FILLER-18 PIC X(01) VALUE ‘,’.
05 ENCT-PLN-TY PIC X(01).
05 FILLER-19 PIC X(01) VALUE ‘,’.
05 ENCT-PLN-NUM PIC X(04).
05 FILLER-20 PIC X(01) VALUE ‘,’.
05 ENCT-LI-NUM PIC 9(03).
05 FILLER-21 PIC X(01) VALUE ‘,’.
05 ENCT-LI-STAT-CD PIC X(01).
05 FILLER-22 PIC X(01) VALUE ‘,’.
05 ENCT-LI-EXC-CD PIC 9(04).
05 FILLER-23 PIC X(01) VALUE ‘,’.
05 ENCT-LI-MC-ENCT-PD-AMT-SGN PIC X(01).
05 ENCT-LI-MC-ENCT-PD-AMT PIC PIC 9(09)V99.
05 FILLER-24 PIC X(01) VALUE ‘,’.
05 ENCT-LI-REIMB-AMT-PD-SGN PIC X(01).
05 ENCT-LI-REIMB-AMT-PD PIC 9(09)V99.
05 FILLER-25 PIC X(01) VALUE ‘,’.
05 ENCT-LI-DUP-TCN PIC X(17).
05 FILLER-26 PIC X(01) VALUE ‘,’.
05 ENCT-LI-DUP-LI PIC 9(03).
05 FILLER-27 PIC X(01) VALUE ‘,’.
05 ENCT-LI-DUP-MCO-TCN-DAT PIC X(20).
Trailer.
01 ENCT-TRL-RECORD. 42 bytes
05 ENCT-TRL-REC-CD PIC X(01) VALUE ‘T’.
05 FILLER-1 PIC X(01) VALUE ‘,’.
05 ENCT-TRL-MC-PROV-ID PIC X(08).
05 FILLER-2 PIC X(01) VALUE ‘,’.
05 ENCT-TRL-BATCH-NUM PIC X(16).
05 FILLER-3 PIC X(01) VALUE ‘,’.
05 ENCT-TRL-REC-TOTAL PIC 9(09).
05 FILLER-4 PIC X(01) VALUE ‘,’.
05 ENCT-TRL-BATCH-TY PIC X(04).
August 11, 2008 at 1:35 am
If you know your header and footer will always be the same size and never the same as any of the detail, you can use the conditional split. This could be risky though.
I have used script tasks in the past to deal with this. Open the file, read lines line by line. You know when you on the first row and when you have no more rows left, you know you on the last row.
as for the signage problem, deal with them as a string and if the sign is a - then multiply the value by -1 else ignore.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 11, 2008 at 7:48 am
Thanks Crispin...................
I have tried my way with some dummy data. let's see how i get my data in the flat file.
First i have a flat file connection which imports all the columns as single columns, so just one row with one columns. Then usinf conditional splits i move into 4 diff , based on H, D , T and default.
Then I have a script componenst to handle each type of data , i.e Header , details and trialer, which i load into 3 loading tables.
Lets hope this will work with real live data.
thanks
August 11, 2008 at 7:58 am
Nothing wrong with doing it your way (provided you can always identify the header and footer) but I would not use a script task in the middle. I would use a derived column and do the splitting / conversion there.
This would be slightly faster if you have a large data set.
Many ways to skin a cat.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply