Trouble importing a large flat file

Question

Trouble importing a large flat file

Viewing 7 posts - 16 through 21 (of 21 total)

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

lmeinke SSCommitted Points: 1956 More actions · Answer 1

Hello All,

I've solved the problem using access as a "pre-import" tool. My question is "Why should I have to do this?"

I could not pinpoint the error in SQL (I realize this is not an impossible task...but it's not simple either) yet Access just hummed right through the whole file?

It seems counter-intuitive that Access can handle this data more eloquently than SQL Server?

I'm still open to any suggestions and since I will be doing this monthly would love to hear any thoughts.

Note: When I tried to use SSIS to import this file and write any errors to an error.txt file it would simply fail at the point of the original errror. The only message could locate told me it had reached the maximum number of failed records (1). No matter how many times in the "Source Editor" dialog I changed the the "set this value to selected cells" to "ignore failure" it would not hold this value. All the columns ("Error", "Truncation") were set to "ignore"....but nothing seemed to work.

lmeinke SSCommitted Points: 1956 More actions · Answer 2

BTW I would like to thank all those who took the time to help.

Thank you,

Lonnie M

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

lmeinke (1/28/2010)
Hello All,
I've solved the problem using access as a "pre-import" tool. My question is "Why should I have to do this?"
I could not pinpoint the error in SQL (I realize this is not an impossible task...but it's not simple either) yet Access just hummed right through the whole file?
It seems counter-intuitive that Access can handle this data more eloquently than SQL Server?
I'm still open to any suggestions and since I will be doing this monthly would love to hear any thoughts.
Note: When I tried to use SSIS to import this file and write any errors to an error.txt file it would simply fail at the point of the original errror. The only message could locate told me it had reached the maximum number of failed records (1). No matter how many times in the "Source Editor" dialog I changed the the "set this value to selected cells" to "ignore failure" it would not hold this value. All the columns ("Error", "Truncation") were set to "ignore"....but nothing seemed to work.

Have you verified IN DETAIL that Access has EVERY BIT OF DATA CORRECTLY?? Perhaps Access is just better at 'ignoring' a problem and skipping over it without notifying you?

I would have done the divide-and-conquer method. Split the file in half, load the first half, probably successfully. Split remaining file in half, load first half. Repead until you get a reasonably small section that fails then open that in textpad and find your problem.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

lmeinke SSCommitted Points: 1956 More actions · Answer 4

That is an excellent point. Unfortunately, I'm not sure I can verify every data point in detail in a file of that size. I can't even export the table back out and compare to the original file as there are many, many columns I didn't import.

Point well made....just don't know how to validate every detail.

Lonnie M

Uripedes Pants Hall of Fame Points: 3641 More actions · Answer 5

When Access finds a row it can't import it will create and write to an Errors table. Check in the table view tab of Access and see if there is a table name "Errors".

Access 2k7 must have changed... in 2k3 it could only handle export of 65k rows.

lmeinke SSCommitted Points: 1956 More actions · Answer 6

There were no error rows reported....no seperate error table. Do I assume then there are no errors???

Thanks,

Lonnie M

Uripedes Pants Hall of Fame Points: 3641 More actions · Answer 7

wow... tough question actually, since we're dealing with Microsoft Office product and I hate to 'assume' anything 😉 MY opinion is that all probably went well, but I'd try to verify it - check # of rows on all 3 - SQL-Serve, Access, and the text file.