Header row is different than detail row

Question

Header row is different than detail row

Chrissy321

SSCoach

Points: 15750
More actions
June 27, 2013 at 4:58 pm

#279184

Hello Folks,
I have a file with a header row that is different than all the other detail rows.
ABC 20130627 123
1|A|20120102|
1|A|20130301|
3|C|20030102|
5|S|20111111|
It looks like the header data is dependent on its position. I'd be happy to jam it all in one column and split on the tsql side.
What's the best way to handle this type of file in SSIS, I'm used to working with files where all lines are consistent.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

brdudley SSCrazy Points: 2320 More actions · Answer 1

Are the column names changing very often? If not, your best bet is probably to ignore the header row and name the columns explicitly. In other words, uncheck Column names in the first data row and specify 1 Header row to skip.

An alternative is to use a Script Component to split each record rather than splitting in T-SQL. It will probably be easier to write and would allow further processing if necessary.

As an aside, did you mean for each record to end with a delimiter? I'm currently working with such a file and haven't seen that before. Perhaps its more common than I thought.

Chrissy321 SSCoach Points: 15750 More actions · Answer 2

I was previously skipping row 1 but now I need to extract some data out of the header.

I'll take a look at script components which I haven't used before.

The file actually does end with the delimiter (and a carriage return). I think its sourced from a old system, perhaps from the mainframe world.

Thanks

brdudley SSCrazy Points: 2320 More actions · Answer 3

Easiest path is probably to switch the file connector to see a single column. Use a Script component and specify it as a transform. The code is very straightforward and there are tons of examples on-line.

I'm interested in your statement that you want some information from the header. That will mean treating it like a data row. The Script task will again help, since you can tell you are on the first record. You might also want to send the header record to a separate output, unless you really want it loaded in the database.

Let me know if you have any difficulties or questions.

Chrissy321 SSCoach Points: 15750 More actions · Answer 4

brdudley (6/28/2013)
Easiest path is probably to switch the file connector to see a single column.

I could bulk insert into a single column and then use the Moden string splitter. If I am going into one column anyway SSIS is not really doing very much for me.

If I was going to use SSIS its almost if I want two data flows, one for the first line and a second that skips the first line.

I'm clearly not a fan of this type of file. I'd rather embed metadata in the file name rather than a header row.

aaron.reese SSChampion Points: 13498 More actions · Answer 5

I would create a second flat file connection for the data source with a single column and then use conditional split to ignore all but first line.

Provided the format of that first line is consistent you can then use string functions (replace, trim, substr) etc to get the data you need.