Bulk Insert Errors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Bulk Insert Errors

Author

Topic

Zix
Starting Member

31 Posts

Posted - 2010-01-18 : 09:25:05

I'm trying to insert some rows into some exisiting tables and keep getting errors. The errors are all variations of...

(Bulk load data conversion error (truncation) for row 13, column 132 (YR)"

The column was created with ...

[YR] [int] NULL ,

This particular record = "0" which should not fail I don't think.

I don't think this is a datatype issues as much as it is a problem reading the tab-delimited flat file that I was sent.

I guess this is a remedial question, but how do I validate a TAB-delimited flat file that has no column headers. I don't even know how many columns I'm supposed to have.

Does that make sense?

Thanks for your help!

Clay

Zix
Starting Member

31 Posts

Posted - 2010-01-18 : 09:27:42

Oh, I'm just using a simple bulk import statement like...

BULK INSERT
[dbo].[RentComm]
FROM 'd:\dir\file.txt'
go

Kristen
Test

22859 Posts

Posted - 2010-01-18 : 10:06:14

I normally pull TAB delimited (or CSV data for that matter) into Excel to review it a bit before trying to use it for anything that is going to barf .

You could pull it into a staging table first, made up of columns defined as varchar(MAX) or something equally "safe" so that the data doesn't get cast, or chopped off, and then you can do things like:


SELECT MAX(DATALENGTH(Col1)) AS COL1MaxLen,
       SUM(IsDate(Col1)) AS COL1DateCount,
       SUM(CASE WHEN Col1 IS NULL THEN 1 ELSE 0 END) AS COL1NullCount,
       SUM(CASE WHEN Col1 = '' THEN 1 ELSE 0 END) AS COL1EmptyCount
FROM MyStagingTable

Zix
Starting Member

31 Posts

Posted - 2010-01-18 : 10:38:14

Good thought Kristen. I *sorta* did that. What do I do if the columns don't match up?

It looks like I'm getting a different number of columns between the tab delimited file (viewing via Excel) and the database table. The data dictionary they sent with the data (column names and descriptions only) shows 134 columns for this particular file/table. I count 134 entries in the CREATE script. But, when I view the table in Excel I count 136 columns.

I first thought that things were not "lining up" correctly. But the last two columns (ZIP and ZIP4) are consistently in the last two places. Otherwise I would expect them to "wrap" from the last into the first columns of the next record.

I know I'm rambling, but just trying to dump out a bunch of info hoping to stumble onto something relevant. :)

Kristen
Test

22859 Posts

Posted - 2010-01-18 : 11:26:39

You'll have to work your way along the 136 columns in Excel and see how they "align" to the data dictionary "spec" they gave you.

power for the course, I'm afraid. But make-hay if it turns out they gave you poor quality DOCs.

Zix
Starting Member

31 Posts

Posted - 2010-01-18 : 12:01:43

Thanks Kristen.

Took the dog for a walk and rebooted my brain. :)

Upon my return, I tried to run the bulk import from the *.xls file rather than from the *.txt file. I actaully only got a few errors doing it this way.

BUT...I get an error on column 1, row 1.

Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 1 (ANC).

The value from the source file is "NULL", whis is allowed.

[ANC] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

I'm clearly doing something wrong but can't seem to get my brain around it.

Kristen
Test

22859 Posts

Posted - 2010-01-18 : 12:11:56

SSIS (well, in SQL 2000 anyway) will only look at the first few rows of the XLS to determine the datatype of the columns (if you let SSIS make a brand new table for you). That can be a source of problems. (Probably not your problems in this issue though)

Have you tried getting SSIS to import into a brand new table and seeing what columns it creates?

(I've assumed until now that you were trying to import into a pre-existing table)

Zix
Starting Member

31 Posts

Posted - 2010-01-18 : 12:25:07

Correct assumption Kristen.

I am running a create table script manually. Then running a bulk import statement.

As far as SSIS...I dunno. It seems to be a bit overkill for this seemingly simple task.

I have data in a file and want to move it into a db table. I can't believe doing that is so difficult in SQL Server 2008.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-18 : 12:40:26

there may be stray <tab> characters in the file which will cause errors.

easiest way when bulk insert is failing is to use the data import wizard and let it create a new table. then you can examine the structure and data easily

Kristen
Test

22859 Posts

Posted - 2010-01-18 : 13:23:04

"Have you tried getting SSIS to import into a brand new table "

Yeah, I meant "Let SSIS create a new table for you ..." but re-reading it it isn't that clear. Thanks Russell.

Zix
Starting Member

31 Posts

Posted - 2010-01-18 : 14:25:57

Yeah, I burned up about a day trying to get the Imp/Exp wizard to work the other day. In fact, I started a few threads on this forum. Was never able to get it to work.

But, to be thorough, I tried again. I dropped the table and ran through the wizard and got a bunch of errors. It pulls in the columns as "Col 0", "Col 1", etc. Also, it types every column as a string. Using the wizard means I would have to relabel every column (about 1500 of them) then set the data type for every one of them. Not practical since this is not a one time occurence.

Again...all I want to do is pull data from a flat file into a db table. I have done this in a ton of other environments. Having trouble understanding why this is so difficult in SQL Server.

Kristen
Test

22859 Posts

Posted - 2010-01-18 : 14:42:39

The purpose of the Create Table was to see what size / type it made the various columns. Sounds like it worked, so perhaps you can relate the columns (and the datatypes of the data they contain) in the table SSIS made to the table you made. If SSIS made them all varchar I suspect that is because the data in them was unreliable/variable (I forget exactly, but I'm pretty sure SSIS sets a datatype based on some analysis of the First-N rows in the import file)

You can put the Column Names into the first row of your XLS [or TXT file for that matter] (hopefully by suitable mechanical-mangling from the list you were given) and SSIS can be told to use those as the column name - which I agree will be better than Col1, Col2, ...

I don't think this will be any harder in SQL server than other environments; if I have understood you correctly you say you pre-created a table with 134 columns but when you pull the same import source file into Excel it has 136 columns. SSIS won't resolve that difference for you! Also, if the datatypes for the columns in the table you have created are wrong for the actual data type, then SSIS won't help there either.

SSIS could give you a really nice clear error message showing the row, and the data in the column it was trying to convert, and an indication of why it couldn't convert it. I think MS should put more effort into that diagnostic, because people waste huge amounts of time trying to resolve WHY an import failed. Dunno how good SSIS for SQL2008 is in this regard, I haven't tried it, but earlier versions of SQL were disappointing.

But to reiterate my earlier post, my experience has been to pull the data into a "bland" VARCHAR table, examine it, query it, and then create the actual Import from there. The DOCs that I have been given have never been accurate enough, on their own, to give me a one-bit-wonder with my coding for an import, and it sounds like what you have been given on this occasion isn't either.

Zix
Starting Member

31 Posts

Posted - 2010-01-19 : 06:56:17

Thanks for the advice Kristen. I can examine the data until my eyes cross but if a value of "0" wont go into an INT column (my original example) then...I don't know.

Thanks again, but I'm not really sure where to go from here.

Kristen
Test

22859 Posts

Posted - 2010-01-19 : 08:15:08

If the data is in a newly created "staging" table (i.e. created by SSIS) can you then do:


INSERT INTO TargetTable
SELECT *
FROM StagingTable

?

I recommend wrapping it in a Transaction to stop it actually running (presumably it will be hard to sort out if part of it runs)


BEGIN TRANSACTION
--
INSERT INTO TargetTable
SELECT *
FROM StagingTable
--
ROLLBACK

then you could do


BEGIN TRANSACTION
--
INSERT INTO MyTargetTable
SELECT *
FROM MyStagingTable
WHERE MyIntColumn = '0'
--
ROLLBACK

to just test rows where that column is '0'

and so on to work out which row is causing the problem.

I'm sure the problem won't actually be trying to store "0" into an INT, but sad-to-say it has been my experience that the error messages don't get you straight to the problem in one strike.

If it was me I would put the data in a staging table and then run tests on the columns to see what column contains illegal data


SELECT TOP 100 MyIntColumn, *
FROM MyStagingTable
WHERE MyIntColumn LIKE '%[^0-9]%' -- Match any non-numeric character

I'll take a side bet that the problem is either in the data you have been given, or the DOCs defining that data

Zix
Starting Member

31 Posts

Posted - 2010-01-19 : 09:42:44

quote:

I'll take a side bet that the problem is either in the data you have been given, or the DOCs defining that data

Or with the guy sitting at the keyboard. :)

Thanks Kristen. Good strategies. I think I'll take this route.

Kristen
Test

22859 Posts

Posted - 2010-01-19 : 10:26:52

I very much doubt its a problem with the guy at the keyboard. This data would have gone into your table if your table matched the data. I doubt your table is wrong, but IME data usually needs sanitising before it matches what the people who gave it to me swore-blind it would "look like".

I predict a "Oh yeah, when there is an R in the month you'll get an invalid INT in that column ..."

Zix
Starting Member

31 Posts

Posted - 2010-01-20 : 08:04:39

Sledgehammer FTW. I went through and changed the data types of all the fields that were failing to [varchar] (255) just to get the data into the table. Once I get all 6 of them done I'll go back and see if I can figure out which records (of the 500,000 or so) are causing the problems. Then I can fix the problems, change the data type back and head to the pub. :)

I normally wouldn't like this solution but I found that I can successfully import a smaller "version" of this data successfully. That leads me to believe that there are anomalous records within the larger set that are failing.

Note...this is a large data set that has smaller updates daily. So this large data set only needs to be inserted once. Then I'm going to download the "daily" files and update the "base" tables based on those changes.

Looks like I'm closing in on it.

Thanks for all the help!

Kristen
Test

22859 Posts

Posted - 2010-01-20 : 12:54:38

"That leads me to believe that there are anomalous records within the larger set that are failing."

OK, I'll leave my money on that square for now then

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources