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

 

[fuzzyocelot]

I'm sorry. I don't think I'm explaining this right. The length of the fields in the header record is different than those in the detail and trailer records. I've been trying to figure out how to write those records to a file and keep their individual lengths intact and to get them into the file correctly. For example, the length of the headr1 and headr2 fields is 5. The length of the detail1 through 5 fields is 6 and for the trailer fields it's 9. If I tried to write the rpt_htd table to a file as it is, wouldn't it put the detail fields to the right of the header fields but on a separate line? Like this:

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

Instead, I need the file to look like this:

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

I hope that makes sense.

Thanks!

 

[Catadmin]

Silly question.

Is this your first time putting this into a package or does this currently exist as a DTS package that you're porting over to SSIS?

I'm just trying to figure out if there's a template out there for you to use from a previous package.



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]

Catadmin, that's not a silly question.

This is a brand new package. It does not currently exist as a DTS package, and I haven't done anything even remotely similar to it before SSIS.

Thanks!

 

[Catadmin]

My original answer to your question was predicated on the thought that you wanted to pad the header and the trailer (or otherwise force them) to be the same sizes as the details. Obviously, my assumption was erroneous.

That being the case, and the fact that this hasn't been done before, I'm thinking there's only two ways to do this. The first way, which you probably don't want to do, is to break it up into 3 separate files.

The second thought I have is to import the data into 3 separate sheets of a single Excel spreadsheet.

And now that I'm thinking about this, I think Fixed Width is out the window with this one. You're either going to have to make it Delimited or Ragged Right. Fixed Width is going to force you to try and make all columns the same width. At least with Delimited, you don't have to worry about that. Not sure what Ragged Right does except it looks cool when previewing data. @=)

So, is Delimited an option here?



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]

Catadmin,

I wish delimited were an option. It would make my life SO much easier! But, unfortunately, it isn't an option. The vendor that will be receiving these files was very specific on the file format and layout. Otherwise, they won't be able to use the files. I have a feeling that I'm going to need to figure out how to use .NET to get this done.

Thanks, though!

 

[fuzzyocelot]

I believe I have finally found a workaround for this problem! Woo hoo! One of our DBAs gave me the idea and so far it works for the most part!

Since the total length of each record in the output file is 800 characters, I created a table with two fields/columns. The first one is the client id and the second one is the "full record". This is what the table looks like:

CREATE TABLE [dbo].[tblClientFile]
(
    chrClientId char(5) NULL, 
    chrFull_Record  char(801)  NULL 
)

I put the create table code (along with code to see if it already exists) in an Execute SQL Task within the control flow. After that is code within another Execute SQL Task to execute a stored procedure I created which populates the table. Within the stored procedure, I created each record formatted as needed and wrote it to the table defined above as one record (chrFull_Record). Then I read the table where chrClientId equals the value I need and wrote the corresponding chrFull_Record to a file using a Data Flow Task. Since I need three files, I have three separate Data Flow Tasks. There's probably a better way of doing this with variables but right now this way seems to work.

I do have ONE more problem, though. It's probably something simple. Within the Data Flow Tasks, I read the table and write the chrFull_Record field to a fixed-width file. For some reason, each record is being written to the same line instead of separate lines. Does anyone know how to fix this? Thanks!


Below is the stored procedure in case anyone is curious:

CREATE PROCEDURE [dbo].[spClientsGenerateFiles](@chrRunDate char(10), @chrClientId char(3),
@chrProdTestInd char(1), @chrFileType char(1), @chrFeedSourceId char(5)) 
AS 
SET NOCOUNT ON

--Declare header, detail, and trailer variables.
DECLARE @chrRecordType char(1), @chrClientName char(60),
@chrDate char(8), @chrYear char(4), @chrMonth char(2), @chrDay char(2), @chrRecordCount char(7), @iCount int

DECLARE @chrEffDate char(8), @chvFamilyId varchar(64), @chvFirstName varchar(64), @chvLastName varchar(64), @chrMiddleInitial char(1), @chrSSN char(9), @chvStreetAddress varchar(128), @chvCity varchar(64), @chrState char(2), @chvZipCode varchar(10), @dtsDateOfBirth smalldatetime, @chrDateOfBirth char(8), @dtsDateEntered smalldatetime,@chrGender char(1)

--Set variables for header record.
SET @chrRecordType = 'H'
SET @chrClientName = 'COMPANY NAME GOES HERE'
SET @chrYear = substring(@chrRunDate, 7, 4)
SET @chrMonth = substring(@chrRunDate, 1, 2)
SET @chrDay = substring(@chrRunDate, 4, 5)
SET @chrDate = @chrYear+REPLICATE('0',2-LEN(@chrMonth))+RTRIM(@chrMonth)+REPLICATE('0',2-LEN(@chrDay))+RTRIM(@chrDay)

--Insert header record into final table.
INSERT INTO dbo.tblClientFile
VALUES(@chrClientId, @chrRecordType + @chrClientId + RTRIM(@chrClientName) + REPLICATE(' ',60-LEN(RTRIM(@chrClientName)))+@chrDate+@chrDate+REPLICATE('0',8)+@chrProdTestInd+'XYZ PLAN'+REPLICATE(' ',18-LEN('XYZ PLAN'))+@chrFeedSourceId+'N'+REPLICATE(' ',8)+REPLICATE(' ',8)+@chrFileType+REPLICATE(' ',670)+'.');

--Set variables and declare cursor for detail records.
--Insert detail record(s).
SET @chrRecordType = 'M'

DECLARE curClients SCROLL CURSOR FOR 
SELECT  c.chvId, c.chvFirstName, c.chvLastName, c.chrMiddleInitial, c.chrSSN, c.chvStreetAddress, c.chvCity, c.chrState, c.chvZipCode, c.dtsDateOfBirth,
c.dtsDateEntered, 'gender'=left(g.chvName,1)
FROM dbo.tblClients c
LEFT JOIN dbo.tblGenders g ON c.inyGenderRcdId = g.inyGenderRcdId
WHERE convert(varchar,c.dtsDateEntered,101) = @chrRunDate 

OPEN curClients

FETCH NEXT FROM curClients
INTO @chvFamilyId, @chvFirstName, @chvLastName, @chrMiddleInitial, @chrSSN, @chvStreetAddress, @chvCity, @chrState, @chvZipCode, @dtsDateOfBirth, @dtsDateEntered, @chrGender

SET @iCount = 0

WHILE @@FETCH_STATUS = 0  
BEGIN
  IF @chvFamilyId IS NOT NULL 
  BEGIN
    SET @chrYear = cast(Year(@dtsDateEntered) as char(4))
    SET @chrMonth = cast(Month(@dtsDateEntered) as char(2))
    SET @chrDay = cast(Day(@dtsDateEntered) as char(2))
    SET @chrEffDate = @chrYear+replicate('0',2-len(rtrim(@chrMonth)))+rtrim(@chrMonth)+replicate('0',2-len(rtrim(@chrDay)))+rtrim(@chrDay)					  
    IF @dtsDateOfBirth Is Not Null 
    BEGIN
      SET @chrYear = cast(Year(@dtsDateOfBirth) as char(4))
      SET @chrMonth = cast(Month(@dtsDateOfBirth) as char(2))
      SET @chrDay = cast(Day(@dtsDateOfBirth) as char(2)) 
      SET @chrDateOfBirth = @chrYear+replicate('0',2-len(rtrim(@chrMonth)))+rtrim(@chrMonth)+replicate('0',2-len(rtrim(@chrDay)))+rtrim(@chrDay)
    END
    ELSE
        SET @chrDateOfBirth = REPLICATE('0',8)
	IF @chvFirstName IS NULL SET @chvFirstName = ''
	IF @chvLastName IS NULL SET @chvLastName = ''
	IF @chrMiddleInitial IS NULL SET @chrMiddleInitial = ''
	IF @chvStreetAddress IS NULL SET @chvStreetAddress = ''
	IF @chvCity IS NULL SET @chvCity = ''
	IF @chrState IS NULL SET @chrState = ''
	IF @chvZipCode IS NULL SET @chvZipCode = ''
	IF @chrSSN IS NULL SET @chrSSN = ''

	INSERT INTO tblClientFile
	VALUES (@chrClientId, @chrRecordType + 
                @chrClientId + REPLICATE(' ',18)+
		RTRIM(@chrSSN)+REPLICATE(' ',18-LEN(RTRIM
                (@chrSSN)))+@chrEffDate+RTRIM
                (@chvFirstName)+REPLICATE(' ',12-LEN(RTRIM
                (@chvFirstName)))+ RTRIM(@chvLastName)
                +REPLICATE(' ',18-LEN(RTRIM(@chvLastName)))
                + RTRIM(@chrMiddleInitial)+REPLICATE(' ',
                1-LEN(RTRIM(@chrMiddleInitial)))+ RTRIM
                (@chvStreetAddress)+REPLICATE(' ',30-LEN
                (RTRIM(@chvStreetAddress)))+ REPLICATE
                (' ',30)+REPLICATE(' ',30)+REPLICATE
                (' ',30)+RTRIM(@chvCity)+REPLICATE(' ',28-
                LEN(RTRIM(@chvCity)))+ RTRIM(@chrState)
                +REPLICATE(' ',2-LEN(RTRIM(@chrState)))+ 
                REPLICATE('0',5-LEN(RTRIM(@chvZipCode)))
                +RTRIM(@chvZipCode)+REPLICATE('0',4)+
		REPLICATE('0',8-LEN(RTRIM
                (@chrDateOfBirth)))+RTRIM(@chrDateOfBirth)+
		CASE ISNULL(@chrGender,'') 
		WHEN 'T' THEN 'U'
		WHEN '' THEN 'U'
		ELSE @chrGender
		END+RTRIM(@chvFamilyId)+REPLICATE(' ',18-
                LEN(RTRIM(@chvFamilyId)))
                + '00000'+'1'+'001'+REPLICATE(' ',15)
                +REPLICATE(' ',18)+REPLICATE('0',8)+
		REPLICATE(' ',18)+REPLICATE('0',8)
                +@chrEffDate+REPLICATE('0',8)+				REPLICATE('0',7)+REPLICATE('0',7)+REPLICATE
                ('0',7)+REPLICATE('0',7)+' '+REPLICATE
                (' ',12)+REPLICATE(' ',1)+REPLICATE('0',7)+
		'N'+'1'+' '+REPLICATE('0',8)+REPLICATE
                ('0',8)+'1'+REPLICATE(' ',62)+
    		REPLICATE(' ',1)+REPLICATE(' ',1)+REPLICATE
                (' ',10)+REPLICATE(' ',2)+
    		REPLICATE('0',9-LEN(RTRIM(@chrSSN)))+RTRIM
                (@chrSSN)+'N'+REPLICATE('0',10)+REPLICATE
                (' ',20)+REPLICATE(' ',20)+
		REPLICATE(' ',20)+REPLICATE(' ',20)
                +REPLICATE(' ',20)+REPLICATE(' ',20)+			REPLICATE(' ',18)+REPLICATE(' ',18)
                +REPLICATE(' ',3)+REPLICATE(' ',119)+'.')
		
		SET @iCount = @iCount + 1
	END
	
	FETCH NEXT FROM curClients
	INTO @chvFamilyId, @chvFirstName, @chvLastName, 
             @chrMiddleInitial, @chrSSN,	 
             @chvStreetAddress, @chvCity, @chrState,     
             @chvZipCode, @dtsDateOfBirth,
	     @dtsDateEntered, @chrGender

END

CLOSE curClients
DEALLOCATE curClients

--Insert trailer record.
SET @chrRecordType = 'T'
SET @chrRecordCount = CAST(@iCount+2 As char(7))

INSERT INTO tblClientFile
VALUES(@chrClientId, @chrRecordType + @chrClientId + REPLICATE('0',7-LEN(RTRIM(@chrRecordCount)))+RTRIM(@chrRecordCount)+REPLICATE(' ',789)+'.');

 

[Catadmin]

Is the stored procedure pulling the "full records" in on separate lines or the same line? If that, then you need to alter the proc.

If not, then look at your flat file connector General Tab & Advanced tab to verify what your end of record delimiter is. The default is {CR}{LF} which is CarriageReturn/LineFeed. Play with those values until you get the results that you need. Change it to {LF} and if that doesn't work, change it to {CR}, etc.

Let us know whether this works or not.



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]

I played around with the end of record delimiter as you suggested and it had no affect on the file. The stored procedure inserts each "full_record" as a separate record into the table. So the table has 9 records/rows.

My data flow source is an OLE DB Source. When I do a preview of the data within the OLE DB Source, it shows 9 rows of data which is correct. When I click on "Preview" in the Flat File Connection Manager Editor, it shows 9 rows. Even under "Columns" it shows separate rows of data and the row width being 800. I also did a preview in the Flat File Destination Editor and it shows 9 rows of data.

I added a Data Viewer and then ran the package. Again, I see 9 rows of data. Why would it write it all the data into one record in the file instead of separate records? I'm definitely missing something here.

Within the flat file connection manager editor:
* the code page is 1252,
* the file format is fixed-width,
* the text qualifier is <none>,
* the header row delimiter is {CR}{LF},
* header rows to skip is 0,
* the OutputColumnWidth is 800 as is the InputColumnWidth,
* the data type is string[DT_STR]

Thanks!

 

[Alan0568]

I have done something almost the same as this using SSIS (if I have understood) I just wrote out to three separate (temp) files. Then consolidated them using an execute process task.

just create a batch file like
--
copy /b header.txt + detail.txt + trailer.txt full.txt
--
and run it using an execute process task

 

[fuzzyocelot]

Hi Alan0568!

That's a good idea! Thanks for sharing! I thought about doing that as well but couldn't figure out how to merge the files together. It sounds like creating the batch file works. Cool!

Catadmin,

I got the data to write to the files on separate rows! I ended up using ragged right instead of fixed-width and that seems to have solved the problem. I'm not sure why it's that way but it works.

Thanks for all your help and everyone else's! If I run into more problems, I'll be sure to post!

Rebecca

 

[Catadmin]

NP. Glad we could all help you out.



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"