Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Flat File Source with Header and Footer Rows Expand / Collapse
Author
Message
Posted Thursday, July 24, 2008 2:03 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 1:23 PM
Points: 336, Visits: 866
Hi All,,
I have a Flat File Source with Header and Footer How can I omit the First and last Row and REad the data from middle by using a SSIS package



---------------------------------------------------

Thanks
Post #540524
Posted Thursday, July 24, 2008 2:45 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, June 20, 2014 5:31 AM
Points: 621, Visits: 345
when you create the Flat File source connection, you can state that the first row is a header record so that will remove the header row. (you can also state how many rows to skip out of the file in the event that the header takes up multiple rows)

the footer is not as simple. is your footer record formatted the same as the body of the data? meaning, if the body of the data is comma delimited, is the footer row the same or is completely different with just textual information?

if it is completely different, if you do the data transform from the Flat File Source into another Data Flow task, the footer record may drop out, from my test it did, but I am not completely sold on this. I would want to handle the bad row in my error handling of the Flat File source to do something with the bad footer record.
Post #540551
Posted Thursday, July 24, 2008 2:45 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 5:18 PM
Points: 226, Visits: 721
On the Flat File connection manager check the 'Column names in the first data row' checkbox. This will tell that you have column names in the first row. But if you just want to skip the first row you can specifty 1 in the 'Header rows to skip' field.

I m not sure how to deal with the footer row, but i am sure you can apply some special logic in your package to either limit the insert or delete the last row when the insert is done.

HTH
~Mukti
Post #540552
Posted Thursday, July 24, 2008 8:13 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 25, 2009 11:35 AM
Points: 61, Visits: 112
you can skip the header row that shouldn't be a problem but skipping a last row is difficult
probably you can try to delete the footer using the script task and then proceed for processing


Regards
Venkat
http://sqlblogging.blogspot.com
Post #540647
Posted Friday, July 25, 2008 6:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 2:30 PM
Points: 104, Visits: 317
How do you distinguish among header/detail/trailer rows? If there is a field that identifies them, you can use it in a conditional split to just keep the detail rows.
Post #540837
Posted Friday, August 1, 2008 1:31 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 25, 2009 11:35 AM
Points: 61, Visits: 112
If no other way to skip then you should use script component as source

Regards
Venkat
http://sqlblogging.blogspot.com
Post #545450
Posted Thursday, December 4, 2008 9:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 24, 2009 12:24 PM
Points: 1, Visits: 4
Hey...
I am trying to figure out the same thing. I do have a field that identifies whether a record is the Header (H), Detail (D) or Trailer (T). However, with that being said, the package still blows up on execution. It gets a validation error telling me that the value being applied to variable emailSubj (which is on that I use in my flow) is mismatched. However, I create a file without the header and trailer and it processes with now problems. So having a field to differentiate what TYPE of record each row is doesn't seem to help.
Post #613879
Posted Thursday, December 4, 2008 4:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 2:30 PM
Points: 104, Visits: 317
Where you read in the file, did you set the error handling to either ignore or reroute bad rows?

The next step should be a conditional split to only pass the rows you want to the rest of your processing.
Post #614185
Posted Wednesday, July 15, 2009 12:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 30, 2014 1:20 PM
Points: 21, Visits: 243
I had a similar issue with consistently two lines of footer at the end of a flat file. What I finally did was use a Script Task, using System.IO, to count the number of lines in the file and then create a new file reading the old minus 2. Not the most ingenuous way of doing things but it has been working just fine every two weeks for the last 16 months.
Post #753705
Posted Wednesday, July 15, 2009 8:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 2:30 PM
Points: 104, Visits: 317
We receive fixed width files that are divided into sections where the section begins with a header row (starts with HD), has zero or more detail rows (starts with UD), and ends with a trailer row (starts with TR). There can be one or more sections in a file and I needed to associate each header and trailer row with the detail rows between them.

Needless to say, the layout of each type of row can vary.

My solution was to read the file as a ragged right file with only one field defined that is more than long enough to cover what we might receive.

I then count what section a row is in. This involves incrementing the section count on a header row or if the previous row was a trailer (they sometimes omit one or the other).

The headers, detail rows, and trailers are written to separate tables along with the section count.

I then run a SQL query to load the header information into the detail rows, followed by another query for the trailers.

Before loading the rows to the tables, I use substring commands to parse the row into individual fields.

You could build the substring commands in Excel easily enough and copy and paste to SSIS. Or if the file layout is already defined in a table, you could write a query to build them. The files I am currently converting to SSIS are already defined in Informatica so I could run a query against the Informatica Repository to get the fields and the substring command.

The first approach I tried involved sending the headers and trailers to a flat file which I would read back in using a connection manager where each field was listed but with a large filler at the end. Then I would load them to tables and read the original file with a connection manager where each detail field was defined. A lookup would be done to the header and trailer tables.

This proved a bad idea when I tried to process a file where the rows were shorter than the start of the last field. SSIS ignored the carriage return/line feed and appended the next row to the end of the row I was reading. So I loaded half the rows.
Post #753928
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse