SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Bulk load data conversion error. and more...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dimitris x
Starting Member

6 Posts

Posted - 07/13/2007 :  11:43:30  Show Profile  Reply with Quote
Hello,
I am working on an application that will import data from ascii tab-delimited files into corresponding tables in a sql server 2005 express db.
The problem I am facing is that i get errors when running bulk insert.
The tables all have one extra column which is a primary key identity value.
Additionaly, the column data types include:
int, bigint, nchar, nvarchar, datetime and bit
an example table looks like this:

CREATE TABLE [dbo].[counties](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[InternalID] [int] NULL,
	[Active] [bit] NULL,
	[Code] [nchar](10) COLLATE Greek_CI_AS NULL,
	[Description] [nvarchar](50) COLLATE Greek_CI_AS NULL,
	[StartDate] [datetime] NULL
)


An example data file looks like this:

InternalID	Active	Code	Description	Date	
1	        1	01	Αιτωλοακαρν	16/11/1909	
2        	1	02	Αργολίδος	29/04/1949	
3           	1	03	Αρκαδίας	16/11/1909	
4         	1	04	Άρτης	        16/11/1909	
5	        1	05	Αττικής	        26/07/1943	

So, what I do is:
1. for each table I generate a character format file with the following command:
bcp mydb..table format nul -f tableformat.fmt -c -T -S host\sqlexpress

2. I modify the format file to exclude the first identity column by zeroing the field length, the column order and terminator. The resulting format file looks like this:

9.0
6
1  SQLCHAR  0  0   ""    0  id          ""
2  SQLCHAR  0  12  "\t"  2  InternalID  ""
3  SQLCHAR  0  3   "\t"  3  Active      ""
4  SQLCHAR  0  20  "\t"  4  Code        Greek_CI_AS
5  SQLCHAR  0  100 "\t"  5  Description Greek_CI_AS
6  SQLCHAR  0  24  "\t"  6  StartDate   ""

3. I run BULK INSERT
BULK INSERT tablename
FROM dataFile
WITH (
      FIRSTROW=2,
      FORMATFILE = formatFile,
      DATAFILETYPE = 'char',
      FIELDTERMINATOR='\t',
      ROWTERMINATOR='\r\n',
      KEEPNULLS
)


As a result of the above configuration I get this:


Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 6 (EndDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 12, column 6 (EndDate).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (10) was exceeded.
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)".


What am I doing wrong here? With previous configurations I got errors about the BIT fields. In general, I only receive errors... The only thing that worked was with a table that only had nvarchar. Is there a fundamental mistake in what i do? I have read many posts, but nothing specific about handling different data types with bcp and bulk insert.
I would appreciate any help, as I am running out of time
Thank you.

Dimitris Chrysomallis

Edited by - dimitris x on 07/13/2007 12:25:18

karuna
Aged Yak Warrior

581 Posts

Posted - 07/13/2007 :  12:28:47  Show Profile  Send karuna a Yahoo! Message  Reply with Quote
I guess it is the issue with datetime format 16/11/1909 (dd/mm/yyyy) while sql server may be expecting in mm/dd/yyyy format.


Thanks
Karunakaran
Go to Top of Page

dimitris x
Starting Member

6 Posts

Posted - 07/13/2007 :  12:59:42  Show Profile  Reply with Quote
quote:
Originally posted by karuna

I guess it is the issue with datetime format 16/11/1909 (dd/mm/yyyy) while sql server may be expecting in mm/dd/yyyy format.


Well, when i run a dts package on the same data and by explicitly setting the date column/field mappings, the import works succesfuly, and the dates values are in dd/mm/yyyy order. I suppose that SET DATEFORMAT might be automaticaly called during conversion, but i am not sure.
Still, how am i supposed to correctly import these data files, given that I can not modify them? Should not the format file handle the mapping in some way? Ok, I can do it with dts manually, but it has to be done programatically.
Thank you for your response
Go to Top of Page

dimitris x
Starting Member

6 Posts

Posted - 07/13/2007 :  13:15:57  Show Profile  Reply with Quote
quote:
Originally posted by karuna

I guess it is the issue with datetime format 16/11/1909 (dd/mm/yyyy) while sql server may be expecting in mm/dd/yyyy format.



Well, I have to say you were absolutely right! I modified the date order and it worked!! Thank you.
But still, is there a way to keep the data as it is and let the server do the conversion? you see, i can not modify the data files.
Go to Top of Page

karuna
Aged Yak Warrior

581 Posts

Posted - 07/13/2007 :  13:21:14  Show Profile  Send karuna a Yahoo! Message  Reply with Quote
Iam not sure if there is a way to specify the format of date in the format file. If there is one, it should solve the issue.
Let me try searching and see if I find something on format for date in the fmt file.

Thanks
Karunakaran
Go to Top of Page

karuna
Aged Yak Warrior

581 Posts

Posted - 07/13/2007 :  17:25:10  Show Profile  Send karuna a Yahoo! Message  Reply with Quote
How about inserting the data to a staging table with varchar datatype and then populate your original destination table?

Thanks
Karunakaran
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000