Load Positional File

Question

Post reply

Load Positional File

BSavoie

SSCertifiable

Points: 6309
More actions
March 7, 2012 at 10:16 pm

#257108

SSIS / SQL 2008R2
I simply need to load a positional file. They are pretty big files. 100000 records or so. I took the easy way out: using a lookup, insert if I don't find the key, otherwise update. I knew it was going to be a slow performer, but it's completely unacceptable. The other three options I'm considering are an instead of trigger to do the upsert, or a change data capture strategy. I'm not using a staging table so the CDC might not be so great an option. Lastly, I saw a clever approach that does the update on the failure constraint of an insert. Seems pretty simple. Seems like I shouldn't have to get that carried away just to load a file. Any advice? There won't be any delete's, only inserts & updates.
Thanks!
.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

What's the question?

What's a positional file?

Can you explain your scenario a bit more?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

BSavoie SSCertifiable Points: 6309 More actions · Answer 2

I've come up with an adequate solution. Thanks.

A positional file is a flat file that contains records of a specific format. That is all record members start at a specific position and occupy a specific length.

I used a lookup to update existing records on the find and insert records on not found. Improved performance immensely. Performance is acceptable now. I'd highly recommend this method to anybody dealing with this problem. Change Data Capture would probably perform even better, but it's a much different pattern and I'd have had to start over which doesn't work for me right now.

However! I ran into another completely different problem with end of line chars in the file. I put that in another post.

.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

BSavoie (3/8/2012)
Performance is acceptable now.

So what is it? What kind of performance did you end up with?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BSavoie SSCertifiable Points: 6309 More actions · Answer 4

The input file has 266,000 420 byte records. I was trying to import it into my SQL Developer instance on my run of the mill 4GB laptop. Using the RBAR OLE DB Command, I gave up after watching it grid for 30 mins. or so. The lookup failure method took about 15-20 seconds.

.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

Ever considered using T-SQL BULK INSERT with a format file?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BSavoie SSCertifiable Points: 6309 More actions · Answer 6

All the mgr. types wanted me to use SSIS, and since I knew I could make that work I didn't argue. Are you suggesting I use SSIS BULK INSERT task, or native TSQL BULK INSERT? I'll definitely take a look. I've never used BULK INSERT with a format file before. Do you think the format file could deal with the inconsistent CR/LF's or would I still have to pre-process the file and strip them out?

.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

BSavoie (3/8/2012)
The input file has 266,000 420 byte records. I was trying to import it into my SQL Developer instance on my run of the mill 4GB laptop. Using the RBAR OLE DB Command, I gave up after watching it grid for 30 mins. or so. The lookup failure method took about 15-20 seconds.

Never ever use the OLE DB command. But you figured that out for yourself 🙂

ps: so I guess with positional file you mean fixed width or ragged right?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 8

Jeff Moden (3/8/2012)
Ever considered using T-SQL BULK INSERT with a format file?

Stop pulling people away from SSIS dammit 🙂

With SSIS you can do your lookups, event handling and logging all in one relatively simple flow. With the BULK INSERT you can just do the import, so you'll need an extra staging table.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP