July 24, 2008 at 2:03 pm
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 [/font]
July 24, 2008 at 2:45 pm
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.
July 24, 2008 at 2:45 pm
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
July 24, 2008 at 8:13 pm
July 25, 2008 at 6:04 am
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.
August 1, 2008 at 1:31 pm
December 4, 2008 at 9:58 am
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.
December 4, 2008 at 4:32 pm
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.
July 15, 2009 at 12:03 pm
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.
July 15, 2009 at 8:57 pm
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply