Does the SQL Import wizard imports data in sequence?

Question

Does the SQL Import wizard imports data in sequence?

halifaxdal

SSCoach

Points: 19741
More actions
November 8, 2016 at 2:45 pm

#314771

Hello,
I am stuck in a 4GB text file import and it stops with 2870269 imported, in order to find out the reason of the showstop, I use EmEditor to open the actual raw data file, it is found that lines after 2870269 were also imported, so my question is:
Does the SQL Import wizard imports data in sequence?
It doesn't seem yes according to my finding but how can I find the problematic line?
Thank you very much.

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

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

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 1

Yes it will process text files in line order, that is no guarantee however that they'll come out of a database table in that order. The line counter can also be somewhat twitchy with large row counts, especially if it crashes while processing. You might be able to try loading from your text file into another text file and seeing where/if it crashes doing that.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

ZZartin (11/8/2016)
Yes it will process text files in line order, that is no guarantee however that they'll come out of a database table in that order. The line counter can also be somewhat twitchy with large row counts, especially if it crashes while processing. You might be able to try loading from your text file into another text file and seeing where/if it crashes doing that.

If you do it with BULK INSERT, an IDENTITY column, and a MAXDOP(1) setting, I can see no reason why it would go out of order.

--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)

halifaxdal SSCoach Points: 19741 More actions · Answer 3

Jeff Moden (11/8/2016)
ZZartin (11/8/2016)
Yes it will process text files in line order, that is no guarantee however that they'll come out of a database table in that order. The line counter can also be somewhat twitchy with large row counts, especially if it crashes while processing. You might be able to try loading from your text file into another text file and seeing where/if it crashes doing that.
If you do it with BULK INSERT, an IDENTITY column, and a MAXDOP(1) setting, I can see no reason why it would go out of order.

That's true, but unfortunately for most of the imports the approach would be using the wizard to get data in place as soon as possible.

BTW, can anyone recommend a good non-SSIS ETL tool for MS SQL? Thanks.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 4

halifaxdal (11/9/2016)
Jeff Moden (11/8/2016)
ZZartin (11/8/2016)
Yes it will process text files in line order, that is no guarantee however that they'll come out of a database table in that order. The line counter can also be somewhat twitchy with large row counts, especially if it crashes while processing. You might be able to try loading from your text file into another text file and seeing where/if it crashes doing that.
If you do it with BULK INSERT, an IDENTITY column, and a MAXDOP(1) setting, I can see no reason why it would go out of order.
That's true, but unfortunately for most of the imports the approach would be using the wizard to get data in place as soon as possible.

If you want to "get data in place as soon as possible", avoid the manual labor and error prone tendencies of the wizard.

BTW, can anyone recommend a good non-SSIS ETL tool for MS SQL?

If the data files are of good quality in their structure, I just did. 😉 If the data files are of unpredictable/questionable structure, then a pork chop launcher and an up-close and personal visit to the people providing the data files is in order.

--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)