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


Header row is different than detail row


Header row is different than detail row

Author
Message
Chrissy321
Chrissy321
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3116 Visits: 4793
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
brdudley
brdudley
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1685 Visits: 1755
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
Chrissy321
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3116 Visits: 4793
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
brdudley
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1685 Visits: 1755
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
Chrissy321
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3116 Visits: 4793
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
aaron.reese
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2409 Visits: 907
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.
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