Can't Get BULK INSERT TO ERROR!!!

Question

Can't Get BULK INSERT TO ERROR!!!

Jacob Pressures

SSCertifiable

Points: 5804
More actions
August 30, 2018 at 9:18 pm

#372248

Hello guy,
I was testing my script for bulk insert. But i can't get the rows to error out and go to an error file. The problem is that call my columns are VARCHAR(255). None of them are numeric or not null. So if i add blank lines, bulk insert just ignores them. If i add extra columns, bulk insert will squeeze them into one column at the end.
Instead, I'd like bulk insert to error and write those non-uniformed columns to an error file.
Can this be made to work or do i have to just live with bulk insert writing junk data into some columns?
Thanks!

Viewing 12 posts - 1 through 11 (of 11 total)

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

sgmunson SSC Guru Points: 110639 More actions · Answer 1

Sorry, but that's not the functionality of BULK INSERT. If you want that kind of row by row examination, you have to use SSIS instead.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

Jacob Pressures - Thursday, August 30, 2018 9:18 PM
Hello guy,
I was testing my script for bulk insert. But i can't get the rows to error out and go to an error file. The problem is that call my columns are VARCHAR(255). None of them are numeric or not null. So if i add blank lines, bulk insert just ignores them. If i add extra columns, bulk insert will squeeze them into one column at the end.
Instead, I'd like bulk insert to error and write those non-uniformed columns to an error file.
Can this be made to work or do i have to just live with bulk insert writing junk data into some columns?
Thanks!

Can you post the BULK INSERT command that you're using? It would also help if you share some example that represents the file you're importing and the DDL for the table.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

andycadley SSCertifiable Points: 5296 More actions · Answer 3

If you're trying to insert a whole bunch of rows and redirect "incorrect" ones according to some ruleset, you may well find SSIS is a better choice since it is pretty much the kind of problem it is designed to solve.

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

You don't need SSIS for this. You need proper constraints on your table and that includes if you're using SSIS. The constraints will cause BULK INSERT to fail and sequester the rows if done properly.

Also, you probably shouldn't be importing to a final table. I always import to a staging table where I can do the data validation in an unfettered manner without conflicting with users of the final table.

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

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 5

Hello guys! Thanks for the feedback. My team decided not to use SSIS at this point.

Here is my table that I made to force errors. But it doesn't input anything unless all the columns are 255. These are close to the right column sizes. This is the staging table. I expect an error file to be created but none are created.Any idea why?

drop TABLE VendorUpc CREATE TABLE [dbo].[VendorUpc]( [ID] [bigint] NOT NULL Identity(1,1) Primary key, [VendorNumber] [varchar](8) NULL, [VendorName] [varchar](50) NULL, [ItemNumber] [varchar](50) NULL, [ProductDescription] [varchar](900) NULL, [UOM] [varchar](255) NULL, ---? [UPC] [varchar](255) NULL ---? ) ON [PRIMARY] GO select * from dbo.VendorUpc

This is the Bulk Insert script

truncate table VendorUpc

insert into VendorUpc ( -- ID - This column value is auto-generated VendorNumber, VendorName, ItemNumber, ProductDescription, UOM, UPC ) select b.VendorNumber, b.VendorName, b.ItemNumber, b.ProductDescription, b.UOMs, b.UPCs from openrowset ( bulk 'F:\Data\UPC\Master_File.txt', formatfile = 'F:\Data\UPC\Format.xml', errorfile = 'F:\Data\UPC\bulk_insert_BadData.txt', firstrow = 1 ) as b

Here is the format file:

<?xml version="1.0" encoding="utf-8"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID ="1" xsi:type="CharTerm" TERMINATOR='|' />
<FIELD ID ="2" xsi:type="CharTerm" TERMINATOR='|' />
<FIELD ID ="3" xsi:type="CharTerm" TERMINATOR='|'/>
<FIELD ID ="4" xsi:type="CharTerm" TERMINATOR='|'/>
<FIELD ID ="5" xsi:type="CharTerm" TERMINATOR='|'/>
<FIELD ID ="6" xsi:type="CharTerm" TERMINATOR='\n'/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="VendorNumber" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="2" NAME="VendorName" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="ItemNumber" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="4" NAME="ProductDescription" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="5" NAME="UOMs" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="6" NAME="UPCs" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

Jacob Pressures - Tuesday, September 4, 2018 10:33 AM
Hello guys! Thanks for the feedback. My team decided not to use SSIS at this point.
Here is my table that I made to force errors. But it doesn't input anything unless all the columns are 255. These are close to the right column sizes. This is the staging table. I expect an error file to be created but none are created.Any idea why?
drop TABLE VendorUpc CREATE TABLE [dbo].[VendorUpc]( [ID] [bigint] NOT NULL Identity(1,1) Primary key, [VendorNumber] [varchar](8) NULL, [VendorName] [varchar](50) NULL, [ItemNumber] [varchar](50) NULL, [ProductDescription] [varchar](900) NULL, [UOM] [varchar](255) NULL, ---? [UPC] [varchar](255) NULL ---? ) ON [PRIMARY] GO select * from dbo.VendorUpc
This is the Bulk Insert script
truncate table VendorUpc
insert into VendorUpc ( -- ID - This column value is auto-generated VendorNumber, VendorName, ItemNumber, ProductDescription, UOM, UPC ) select b.VendorNumber, b.VendorName, b.ItemNumber, b.ProductDescription, b.UOMs, b.UPCs from openrowset ( bulk 'F:\Data\UPC\Master_File.txt', formatfile = 'F:\Data\UPC\Format.xml', errorfile = 'F:\Data\UPC\bulk_insert_BadData.txt', firstrow = 1 ) as b
Here is the format file:
<?xml version="1.0" encoding="utf-8"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID ="1" xsi:type="CharTerm" TERMINATOR='|' />
<FIELD ID ="2" xsi:type="CharTerm" TERMINATOR='|' />
<FIELD ID ="3" xsi:type="CharTerm" TERMINATOR='|'/>
<FIELD ID ="4" xsi:type="CharTerm" TERMINATOR='|'/>
<FIELD ID ="5" xsi:type="CharTerm" TERMINATOR='|'/>
<FIELD ID ="6" xsi:type="CharTerm" TERMINATOR='\n'/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="VendorNumber" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="2" NAME="VendorName" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="ItemNumber" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="4" NAME="ProductDescription" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="5" NAME="UOMs" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="6" NAME="UPCs" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>
Sample Data:
WT1900|TrueWater|600|"3/4"" Anti-siphon body less union"|Piece|843687114345
WT1900|TrueWater|601|"3/4"" Anti-siphon body with union"|Piece|843687114383
WT1900|TrueWater|602|"1"" Anti-siphon body less union"|Piece|843687114369
WT1900|TrueWater|603|"1"" Anti-siphon body with union"|Piece|843687114406
SUN333|Sunny Bay|604|"3/4"" AVB only"|Piece|843687122029
SUN333|Sunny Bay|605|"1"" AVB only"|Piece|843687122036
SUN333|Sunny Bay|606|"1-1/4"" AV & AVB"|Piece|843687122043
SUN333|Sunny Bay|607|"1-1/2"" AV & AVB"|Piece|843687122050
HD9319|Home Decor|608|"2"" AV & AVB"|Piece|843687122067
HD9319|Home Decor|609|"3/4"" AVU only"|Piece|843687122074

Heh... bloody XML format files. I'll try to bet back after work on this. This is actually pretty easy and I wouldn't be at all surprised if someone beat me to it. There's no reason to use OPENROWSET for this unless the data is on another machine that hasn't been setup with the correct privs.

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

andycadley SSCertifiable Points: 5296 More actions · Answer 7

You need to set MAX_LENGTH on all the FIELD elements in the format file or OpenRowset/Bulk Insert just assumes they will work.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

Is the production description field in the file ALWAYS encapsulated in double quotes?

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

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 9

AndyCadley and Jeff Moden,

Hey, Thanks guys!

AndyCadley, I'll try to look it up, but why do i need MAX_LENGTH? I'll look it up but I'm just wondering. It works perfectly fine when I make the staging table VARCHAR(255).

Jeff Moden, currently its a relic. I don't assume the quotes will always be there. In fact I asked the person who produces the txt files to remove them.

Thanks guys!

andycadley SSCertifiable Points: 5296 More actions · Answer 10

Because the data entirely fits in a VC(255). Redirecting errors via Bulk Insert only redirects rows that don't comply with the Format File. Once that is processed, it just does an insert for all "valid" rows and fails the operation if Table Constraints/Column sizes etc stop the insert occurring.

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 11

AndyCadley,

YOU WERE RIGHT!!!!!
Thanks A million man! I'm getting the error file now!

But now I don't know why it is producing an error. All my columns are the correct length.

Oh and Thanks for your reply. I'm just seeing this. I didn't see that there was a second page.

This is my error message:

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