Problem Getting Header, Detail, Trailer Records Into Same File

[fuzzyocelot]

I'm running SQL Server 2005 locally on my computer under Windows XP. I'm new to SQL Server 2005 and SSIS, although I've used SQL Server 2000 DTS for the last year. Here is my problem that I hope someone will be able to help me with.

I need to get the data from three different database tables into the same file three times with a few variations. The first table contains 3 header records. The second table contains many detail records. The third table contains 3 trailer records. I need to write the correct header record to the correct file followed by the corresponding detail records and then the corresponding trailer record. The records must be fixed length in the file. The biggest problem I'm having is that the column lengths between the header, detail, and trailer records are different. However, the total length of every record is 800 characters. So, for example, in the header records I have 14 columns and they do not "line up" with the detail or trailer columns. The detail records have 60 columns and the trailer records have 4 columns.

So far in my package, I have a flat file connection for one file. I have three data flow tasks, one for the header table, one for the detail table, and one for the trailer table. Within each data flow task is an OLE DB source connection to the database table (i.e. header table, etc.) and a flat file connection to the same file. I'm now stuck. I have no idea how to get this to work the way I need it to. I tried specifying the columns I need in the flat file connection, but I can't get it to account for the different column lengths per type of record. Also right now it's just writing everything to one record instead of multiple records because of that setting and the mapping.

The header and trailer records don't necessarily have to be in database tables. If necessary, I could generate them some other way. I hope I've provided enough information. If not, please let me know and I'll give you more information. I would really appreciate any help or advice or guidance!

Thanks!
Rebecca

 

[Catadmin]

We're all kind of new to SSIS, but hopefully we can help you out.

My suggestion is to pull the header data into a Temp Table, then use an Execute SQL Task to convert the Header rows into datatypes of the same length as your details & Trailer rows. If your Header is a char(10) and you need it to be a char(50), just use the CONVERT function (or even the CAST function) to increase the size of it. This should add blank spaces to right side of the information in the fields.

Once this is done, import the Header rows into the table where everything is supposed to be stored, then import your Detail & Trailer rows. Do the same type of conversion on them if they are different sizes also.

You could also use a VB .Net script, but that gets complicated if you don't know VB .Net.

Does this help?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[fuzzyocelot]

Thank you for your response! I really do appreciate it.

That sort of helps. The total length for the header, detail, and trailer records are the same (800 characters). The problem is that the individual header columns/fields are different than the other records/rows and must stay that way. They all must maintain their lengths. So if the first column/field in the header row is char(10), it must stay that way. If the first column/field in the detail row is char(50), it must stay that way and so on.

In a way, I was hoping I wasn't going to have to use VB.Net because I don't know it yet. However,it looks like I may have no choice. I do know VB 6 but from what I've read VB.Net seems quite a bit different.

Thanks!

 

[Catadmin]

If you're that leery about VB .Net, get an SSIS book and look at all the different transformation tasks. I'm sure you can do what you want without resorting to VB .Net script.

Don't forget that you can take the fields and use a Merge or Merge Join transformation to put everything back together again once you're done with changing the column sizes.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[manmaria]

Don't you have a common field for Header,Trailer and details? I think there should be one, if it is then you can join by the common key to get all the information together.
Give some more details.

 

[fuzzyocelot]

I'm not having a problem with joining the tables to make the files. There is a common field and I can join them without any problems. The problem I am having is in keeping the fields their original length in the files. The fields are fixed length but different. So, for example, if the first field in the header record is char(10), it must stay that way. If the first field in the detail row is char(50), it must stay that way and so on.

 

[manmaria]

Thanks. Give the table layouts with the common field. Let me try the SSIS.

Table1: details
Table2: details
Table3: details

Output: Flat file: details

 

[fuzzyocelot]

manmaria, I will be more than happy to give you the table layouts. Unfortunately, I'm on vacation this week and the layouts are at work. When I'm back at work next week, I'll post the exact layouts. Thanks!

 

[fuzzyocelot]

Sorry it took so long for me to get back. Below is a partial layout of the detail table and output files. I didn't post the layouts in their entirety because I figured the partial will give you a good idea of how the files need to look. The header record has a total of 14 fields, the detail record has 60 fields, and the trailer record has 4 fields. The header record and trailer record do not currently exist in a table. So I can generate these records dynamically, if necessary. The detail records are in a SQL Server table but do not have the client id. I just have to retrieve all of the detail records by effective date and create the files. I really appreciate any more help or advice anyone has. Thanks!

[b]Detail Table: Clients [/b](note: the table actually has 70 columns)
[u]Column Name[/u][tab][tab][u]Type[/u][tab][tab][u]Length[/u]
intRecordId        int            4
chvId              varchar        64
chvFirstName       varchar        64
chvLastName        varchar        64
chrSSN             char           9
dtsEffectiveDate   smalldatetime  4
etc.

[b]Example of a detail record from the table:[/b]
[u]intRecordId[/u][tab][u]chvId[/u][tab][u]chvFirstName[/u][tab][u]chvLastName[/u][tab][u]chrSSN[/u][tab][tab][u]dtsEffectiveDate[/u]
21             A38      Joe             Smith          555-55-5555    20060817 

[b]Output File Layout:[/b]

[b]Header Record:[/b]
[u]Field Number[/u][tab][u]Field Name[/u][tab][u]Field Length[/u][tab][u]Position[/u][tab][u]Value[/u]
1                Record Type     1            1-1         "H" (header)
2                Client Id       3            2-4         "V2R"
3                Client Name     60           5-64        "company name"
4                Processing Date 8            65-72       "20060817"
etc.
14               Filler          670          131-800     spaces

[b]Detail Record:[/b]
[u]Field Number[/u][tab][u]Field Name[/u][tab][u]Field Length[/u][tab][u]Position[/u][tab][u]Value[/u]
1                Record Type          1        1-1        "M" (member)
2                Client Id            3        2-4        "V2R"
3                Id#1                 18       5-22       "111-11"
4                Id#2                 18       23-40      "555-55-5555"
5                Effective Date       8        41-48      "20060817"
6                First Name           12       49-60      "Joe"
etc.
60               Filler               119      682-800     spaces

[b]Trailer Record:[/b]
[u]Field Number[/u][tab][u]Field Name[/u][tab][u]Field Length[/u][tab][u]Position[/u][tab][u]Value[/u]
1                Record Type         1        1-1        "T" (trailer)
2                Client Id           3        2-4        "V2R"
3                Record Count        7        5-11       "3"
4                Filler              789      12-800     spaces

 

[manmaria]

Very good. Now, more questions (Sorry, I have to ask before I put my effort) for clarity.

You have 3 different record type files and you want to join them based on the record type. Why you have three header files? What is the difference between these 3 header files? What makes that headerrecord to be unique? Is that ClientId byiteself is unique? If so, then for the same clientid header record, you want to display all the details record followed by trailer for the same client. And you want to repeat this 3 times. Are we clear?

 

[fuzzyocelot]

No need to apologize for the questions. I am more than happy to give you the info you need.

You're pretty much correct. The difference between the header files is the client id. For the first file the client id is "V2R", for the second file it's "V3R", and for the third file it's "V4R". The detail and trailer records contain the same client id that is in the header record. The header record needs to be written to the file first followed by the detail records and one trailer record at the end. There needs to be three separate files that contain the same information. The difference between the files is this client id. The header and trailer records do not currently exist in a table, but I could always create one or two tables if necessary. I hope this makes sense.

Thanks!

 

[manmaria]

Here you go (Post1)

CREATE TABLE Header
(
Client_Id char(18) NOT NULL ,
Record_Type char(5) NULL ,
Headr1 char(5) NULL ,
Headr2 char(5) NULL
)
go

CREATE TABLE Details
(
Client_Id char(18) NOT NULL ,
Record_Type char(5) NULL ,
Detail1 char(3) NULL ,
Detail2 char(3) NULL ,
Detail3 char(3) NULL ,
Detail4 char(3) NULL ,
Detail5 char(3) NULL
)
go

CREATE TABLE Trailer
(
Client_Id char(18) NOT NULL ,
Record_Type char(5) NULL ,
Trail1 char(2) NULL ,
Trail2 char(2) NULL ,
Trail3 char(2) NULL
)
go

CREATE TABLE HTD
(
Client_Id char(18) NOT NULL ,
Record_Type char(5) NULL ,
Headr1 char(5) NULL ,
Headr2 char(5) NULL ,
Detail1 char(18) NULL ,
Detail2 char(18) NULL ,
Detail3 char(18) NULL ,
Detail4 char(18) NULL ,
Detail5 char(18) NULL ,
Trail1 char(18) NULL ,
Trail2 char(18) NULL ,
Trail3 char(18) NULL
)
go


CREATE TABLE RPT_HTD
(
Client_Id char(18) NOT NULL ,
Record_Type char(5) NULL ,
Headr1 char(5) NULL ,
Headr2 char(5) NULL ,
Detail1 char(18) NULL ,
Detail2 char(18) NULL ,
Detail3 char(18) NULL ,
Detail4 char(18) NULL ,
Detail5 char(18) NULL ,
Trail1 char(18) NULL ,
Trail2 char(18) NULL ,
Trail3 char(18) NULL
)
go

 

[manmaria]

(Post2)

Insert into dbo.Header (Client_Id, Record_Type, Headr1, Headr2) VALUES('C1', 'H', 'C1H1', 'C1H1')
;
Insert into dbo.Header (Client_Id, Record_Type, Headr1, Headr2) VALUES('C2', 'H', 'C2H2', 'C2H2')
;
Insert into dbo.Header (Client_Id, Record_Type, Headr1, Headr2) VALUES('C3', 'H', 'C3H3', 'C3H3')



Insert into dbo.Details (Client_Id, Record_Type, Detail1, Detail2, Detail3, Detail4, Detail5) VALUES('C1', 'D', 'C1D1', 'C1D1', 'C1D1', 'C1D1', 'C1D1')
;
Insert into dbo.Details (Client_Id, Record_Type, Detail1, Detail2, Detail3, Detail4, Detail5) VALUES('C1', 'D', 'C1D2', 'C1D2', 'C1D2', 'C1D2', 'C1D2')
;
Insert into dbo.Details (Client_Id, Record_Type, Detail1, Detail2, Detail3, Detail4, Detail5) VALUES('C2', 'D', 'C2D3', 'C2D3', 'C2D3', 'C2D3', 'C2D3')
;

Insert into Trailer (Client_Id, Record_Type, Trail1, Trail2, Trail3) VALUES('C1', 'T', 'C1T1', 'C1T1', 'C1T1')
;
Insert into Trailer (Client_Id, Record_Type, Trail1, Trail2, Trail3) VALUES('C2', 'T', 'C2T2', 'C2T2', 'C2T2')
;
Insert into Trailer (Client_Id, Record_Type, Trail1, Trail2, Trail3) VALUES('C3', 'T', 'C3T3', 'C3T3', 'C3T3')

;

INSERT INTO HTD
(HTD.Client_Id, HTD.Record_Type , HTD.Headr1, HTD.Headr2 ,
HTD.Detail1 , HTD.Detail2 , HTD.Detail3 , HTD.Detail4 , HTD.Detail5 ,
HTD.Trail1 , HTD.Trail2 , HTD.Trail3)

SELECT Header.Client_Id AS EXPR1, Header.Record_Type AS EXPR3, Header.Headr1, Header.Headr2,
Details.Detail1, Details.Detail2, Details.Detail3, Details.Detail4, Details.Detail5,
Trailer.Trail1, Trailer.Trail2, Trailer.Trail3
FROM Header FULL OUTER JOIN
Details ON Header.Client_Id =Details.Client_Id FULL OUTER JOIN
Trailer ON Header.Client_Id = Trailer.Client_Id
;

INSERT INTO
RPT_HTD

select DISTINCT Client_Id, Record_Type, Headr1, Headr2,'' as Detail1,'' Detail2,'' as Detail3 ,'' as Detail4 ,'' as Detail5 ,
'' as Trail1 ,'' as Trail2 ,'' as Trail3 from HTD
UNION ALL
SELECT Client_Id, Record_Type,'' as Headr1,'' as Headr2, Detail1, Detail2, Detail3, Detail4, Detail5,
'' as Trail1 ,'' as Trail2 ,'' as Trail3
from HTD
UNION ALL
SELECT Client_Id, Record_Type,'' as Headr1,'' as Headr2,
'' as Detail1,''as Detail2,''as Detail3,'' as Detail4,'' as Detail5,
Trail1, Trail2, Trail3 FROM HTD

 

[manmaria]

(Post3)

select data from RPT_HTD and filter out however you want. I think there is a problem somewhere with Recordtype but I think it is not a bigdeal for your case.

Basic idea is your Header,Details,Trailers as one common table with all the information and the common key clientid.

RPT_HTD is for writing the data however you want.

HTH
: )

 

[fuzzyocelot]

I'll give it a try and let you know how it goes!

Thanks!!

 

[manmaria]

I have expanded the filed sizes in Details,Trailers to fit the data. Sorry about that. But please do expand to fit 4 chars.


CREATE TABLE Details
(
Client_Id char(18) NOT NULL ,
Record_Type char(5) NULL ,
Detail1 char(4) NULL ,
Detail2 char(4) NULL ,
Detail3 char(4) NULL ,
Detail4 char(4) NULL ,
Detail5 char(4) NULL
)
go

CREATE TABLE Trailer
(
Client_Id char(18) NOT NULL ,
Record_Type char(5) NULL ,
Trail1 char(9) NULL ,
Trail2 char(9) NULL ,
Trail3 char(9) NULL
)
go


Actually, when I thinkthru I think this is nothing but PIVOT/UNIPIVOT. I will try sometime later if do not get it.

 

[fuzzyocelot]

manmaria,

I tried your suggestion. I think we're getting closer to figuring this out, but I don't think we're quite there yet. I might be missing something, though. When I tried your suggestion, I modified it a bit to illustrate a few things. I changed all the client_id field lengths to 3, the record_type field lengths to 3, and the detail field lengths to 6.

Following are the results from the RPT_HTD table (select * from rpt_htd where client_id = 'C1'):

Client_Id   Record_Type  Headr1  Headr2  Detail1  Detail2  Detail3  Detail4  Detail5  Trail1  Trail2  Trail3
C1          H            C1H1    C1H1      
C1          H                            C1D1     C1D1     C1D1     C1D1     C1D1     
C1          H                                                                         C1T1    C1T1    C1T1

If the 'C1' records were to go into a file, the output file would need to look like the following:

C1 H  C1H1 C1H1 
C1 D  C1D1  C1D1  C1D1  C1D1  C1D1     
C1 D  C1D2  C1D2  C1D2  C1D2  C1D2     
C1 D  C1D3  C1D3  C1D3  C1D3  C1D3     
C1 T  C1T1     C1T1     C1T1

The record type has to be 'H' for the header record, 'D' for the detail records, and 'T' for the trailer record. The fields also must have the correct length in the file. For example, the header fields are char(5) and have to be that way in the file (space filled). The detail fields are char(6) and must keep that length in the file, and so on. This is the part that I'm really having trouble with.

Any ideas on how to get that data into a file using the layout above? I really do appreciate your help so far.

Thanks!

 

[manmaria]

In the insert sql to RPT_HTD, hardcode the recordtype to 'H' for the first insert and 'D' for second insert statment and 'T' for the third statement. Once we know the record type then it is easy to extract the data.

Like
select * from RPT_HTD where Record_Type='H' and ClientId='C1'
union all
Select * from RPT_HTD where Record_Type='D' and ClientID='C1'
UNION ALL
select * from RPT_HTD where Record_Type='T' and ClientID='C1'

 

[fuzzyocelot]

You're right about the record type! Duh! Now do you have any ideas about how to get the data into a file keeping the fields' length fixed?

Thanks!

 

[manmaria]

What do you mean by fixed? I thought they are all fixed. You can define the columns of header,details,trailer however you want. I have defined all of them as char(5) or char(18) just for example but you can change them, if I understand.