SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Flat File Source with Header and Footer Rows


Flat File Source with Header and Footer Rows

Author
Message
sql_learner29
sql_learner29
Say Hey Kid
Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)

Group: General Forum Members
Points: 697 Visits: 922
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

Chuck Rivel
Chuck Rivel
Right there with Babe
Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)

Group: General Forum Members
Points: 719 Visits: 357
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.
Mukti
Mukti
Old Hand
Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)

Group: General Forum Members
Points: 350 Visits: 747
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
SrikanthSv
SrikanthSv
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 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
kbatta
kbatta
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 336
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.
SrikanthSv
SrikanthSv
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 112
If no other way to skip then you should use script component as source

Regards
Venkat
http://sqlblogging.blogspot.com
kent.hogeland-958432
kent.hogeland-958432
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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.
kbatta
kbatta
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 336
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.
Jim McCoy-259251
Jim McCoy-259251
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 261
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.
kbatta
kbatta
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 336
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search