question

I hope someone can help with my dilemna. It's probably super simple that i'm missing but I need to get to the bottom of it so I can do future bulk loads. Problem: When using bulk insert with a format file (created by bcp). format file sample: 9.0 29 1 SQLINT 0 4 "," 1 PERSONID "" 2 SQLINT 0 4 "," 2 SecondID "" 3 SQLNCHAR 2 10 "," 3 Field1 4 SQLNCHAR 2 10 "," 4 Field2 ... 29 SQLNCHAR 2 200 "\r\n" 29 Field29 **Example data:** 1111111,4,NO,NO,NO,YES,NO,1111111,111111,111111,Hourly - Active,#222222222222222222,,,,,,,,,,,,,Lots of text here,#222222222222222222,08/31/2011,, There error says the column row one is being truncated due to conversion failure. When I use the SSMS import wizard it sees the 1st two columns as float. If I'm using a format file to force sql to use a certain data type why is still trying to convert these columns to something else? I hope there is enough here to explain the problem. Anyway help would be greatly appreciated as I do not want to keep importing these files into our production database. I would like to use the bulk insert into a temp table, do my updating as necessary, then remove the temp table. Thanks so much! K **Additional Information (16SEP2011)** Create table script: create table #TempTable ( PERSONID int, Secondid int, Field1 NVARCHAR(5), Field2 NVARCHAR(5), Field3 NVARCHAR(5), Field4 NVARCHAR(5), Field5 NVARCHAR(5), Field6 NVARCHAR(10), Field7 NVARCHAR(10), Field8 NVARCHAR(10), Field9 NVARCHAR(50), Field10 NVARCHAR(100) , Field11 NVARCHAR(50), Field12 NVARCHAR(100) , Field13 NVARCHAR(50), Field14 NVARCHAR(100), Field15 NVARCHAR(50), Field16 NVARCHAR(100), Field17 NVARCHAR(50), Field18 NVARCHAR(5), Field19 NVARCHAR(5), Field20 NVARCHAR(5), Field21 NVARCHAR(50), Field22 NVARCHAR(50), Field23 nvarchar(100), Field24 nvarchar(100), Field25 nvarchar(100), Field26 nvarchar(100) , Field27 nvarchar(100) ) --grab the data into the temporary table BULK INSERT #TempTable FROM 'D:sqlupdatesSampleData.csv' WITH (FORMATFILE='D:sqlupdatesSampleData-n.fmt') --Specify filename to use here select * from #TempTable Here is the bcp syntax used to create the format file: bcp AdventureWorks2008R2.HumanResources.Department format nul -T -n -f Department-n.fmt -i used native (-n) because the character version wasn't working plus i have to do an inner join on the personid which is an int, so I was trying to save myself from doing a cast after the fact. Format file: 9.0 29 1 SQLINT 0 4 "," 1 PERSONID "" 2 SQLINT 0 4 "," 2 SecondId "" 3 SQLNCHAR 2 10 "," 3 Field1 SQL_Latin1_General_CP1_CI_AS 4 SQLNCHAR 2 10 "," 4 Field2 SQL_Latin1_General_CP1_CI_AS 5 SQLNCHAR 2 10 "," 5 Field3 SQL_Latin1_General_CP1_CI_AS 6 SQLNCHAR 2 10 "," 6 Field4 SQL_Latin1_General_CP1_CI_AS 7 SQLNCHAR 2 10 "," 7 Field5 SQL_Latin1_General_CP1_CI_AS 8 SQLNCHAR 2 20 "," 8 Field6 SQL_Latin1_General_CP1_CI_AS 9 SQLNCHAR 2 20 "," 9 Field7 SQL_Latin1_General_CP1_CI_AS 10 SQLNCHAR 2 20 "," 10 Field8 SQL_Latin1_General_CP1_CI_AS 11 SQLNCHAR 2 100 "," 11 Field9 SQL_Latin1_General_CP1_CI_AS 12 SQLNCHAR 2 200 "," 12 Field10 SQL_Latin1_General_CP1_CI_AS 13 SQLNCHAR 2 100 "," 13 Field11 SQL_Latin1_General_CP1_CI_AS 14 SQLNCHAR 2 200 "," 14 Field12 SQL_Latin1_General_CP1_CI_AS 15 SQLNCHAR 2 100 "," 15 Field13 SQL_Latin1_General_CP1_CI_AS 16 SQLNCHAR 2 200 "," 16 Field14 SQL_Latin1_General_CP1_CI_AS 17 SQLNCHAR 2 100 "," 17 Field15 SQL_Latin1_General_CP1_CI_AS 18 SQLNCHAR 2 200 "," 18 Field16 SQL_Latin1_General_CP1_CI_AS 19 SQLNCHAR 2 100 "," 19 Field17 SQL_Latin1_General_CP1_CI_AS 20 SQLNCHAR 2 10 "," 20 Field18 SQL_Latin1_General_CP1_CI_AS 21 SQLNCHAR 2 10 "," 21 Field19 SQL_Latin1_General_CP1_CI_AS 22 SQLNCHAR 2 10 "," 22 Field20 SQL_Latin1_General_CP1_CI_AS 23 SQLNCHAR 2 100 "," 23 Field21 SQL_Latin1_General_CP1_CI_AS 24 SQLNCHAR 2 100 "," 24 Field22 SQL_Latin1_General_CP1_CI_AS 25 SQLNCHAR 2 200 "," 25 Field23 SQL_Latin1_General_CP1_CI_AS 26 SQLNCHAR 2 200 "," 26 Field24 SQL_Latin1_General_CP1_CI_AS 27 SQLNCHAR 2 200 "," 27 Field25 SQL_Latin1_General_CP1_CI_AS 28 SQLNCHAR 2 200 "," 28 Field26 SQL_Latin1_General_CP1_CI_AS 29 SQLNCHAR 2 200 "rn" 29 Field27 SQL_Latin1_General_CP1_CI_AS Sample data: 1111111,4,NO,NO,NO,YES,NO,0011001,011110,1234,Hourly - Active,#9101111111111111111111,,,,,,,,,,,,,There is a long string of text here. xxxxxx,#9101111111111111111111,08/31/2011,, 2222222,47,NO,NO,YES,NO,NO,0022002,022220,5678,Salaried - Active,#9101111111111111111111,,,,,,,,,,,,,There is a long string of text here. xxxxxx,#9101111111111111111111,08/31/2011,, 3333333,15,NO,NO,YES,NO,NO,0033003,033330,9101,Salaried - Active,#9101111111111111111111,,,,,,,,,,,,,There is a long string of text here. xxxxxx,#9101111111111111111111,08/31/2011,, error message: Msg 4863, Level 16, State 4, Line 1 Bulk load data conversion error (truncation) for row 1, column 1 (PERSONID). Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7330, Level 16, State 2, Line 1 Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)". Ok - I hope this gives enough info to resolve this. Thanks!! K