I have created a XML format file and am getting the following error.
Bulk load data conversion error (truncation) for row 1, column 7 (linememo1).
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
Below is my bulk insert code.
BULK INSERT CorpBilling.dbo.BillingImports FROM '\\lifedev2012\Data\Everyone\BILLMEIMPORT\EPISYSFEED.TXT' WITH (FORMATFILE='\\lifedev2012\Data\Everyone\BILLMEIMPORT\blimport.fmt' , KEEPNULLS, ROWTERMINATOR='\n\r', FIELDTERMINATOR='')
Below is my format XML file.
<?xml version="1.0"?> <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="CharFixed" LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="2" xsi:type="CharFixed" LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="3" xsi:type="CharFixed" LENGTH="9" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="4" xsi:type="CharFixed" LENGTH="9" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="5" xsi:type="NativeFixed" LENGTH="8"/> <FIELD ID="6" xsi:type="NativeFixed" LENGTH="11"/> <FIELD ID="7" xsi:type="NCharFixed" LENGTH="35" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="8" xsi:type="NCharFixed" LENGTH="35" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="9" xsi:type="NCharFixed" LENGTH="35" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> </RECORD> <ROW> <COLUMN SOURCE="1" NAME="pgrpcd" xsi:type="SQLVARYCHAR"/> <COLUMN SOURCE="2" NAME="pitmcd" xsi:type="SQLVARYCHAR"/> <COLUMN SOURCE="3" NAME="corpnbr" xsi:type="SQLVARYCHAR"/> <COLUMN SOURCE="4" NAME="cunbr" xsi:type="SQLVARYCHAR"/> <COLUMN SOURCE="5" NAME="qty" xsi:type="SQLDECIMAL" PRECISION="8" SCALE="1"/> <COLUMN SOURCE="6" NAME="uprc" xsi:type="SQLDECIMAL" PRECISION="11" SCALE="4"/> <COLUMN SOURCE="7" NAME="linememo1" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="8" NAME="linememo2" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="9" NAME="linememo3" xsi:type="SQLNVARCHAR"/> </ROW> </BCPFORMAT>
The import file is a fixed length file of 150 characters plus CrLf at the end of each line. Below is the table definition for the table I am trying to import into.
CREATE TABLE [dbo].[BillingImports]( [pgrpcd] [varchar](4) NULL, [pitmcd] [varchar](4) NULL, [corpnbr] [varchar](9) NULL, [cunbr] [varchar](9) NULL, [qty] [decimal](8, 1) NULL, [uprc] [decimal](11, 4) NULL, [linememo1] [nvarchar](35) NULL, [linememo2] [nvarchar](35) NULL, [linememo3] [nvarchar](35) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[BillingImports] ADD CONSTRAINT [DF_BillingImports_qty] DEFAULT ((0)) FOR [qty] GO ALTER TABLE [dbo].[BillingImports] ADD CONSTRAINT [DF_BillingImports_uprc] DEFAULT ((0)) FOR [uprc] GO