Bulk Insert Failing - 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 Failing

Author

Topic

tmccar
Starting Member

27 Posts

Posted - 2011-09-08 : 08:19:46

I'm doing a BULK INSERT using the following code:

BULK INSERT dbo.Drawings
FROM 'C:\extracteddata\Drawings.txt'
WITH (FORMATFILE = 'C:\extracteddata\CURRENCY.xml');
GO
SELECT * FROM DBO.Drawings;
GO

and it exits with this error:
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 28, column 49 (Record_last_updated)

I believe the issue is that the date is in the wrong format. (In the XML file the field format is "SQLDATETIME")
What's the best way to correct this?

desperately seeking data

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-08 : 12:49:42

Try using varchar data type. If you can't change the data type in Drawings table, then you'll need to import this into a staging table that has the data types that'll allow you to do the import. Once you have it in the staging table, you can then fix the data using T-SQL and then move your data to Drawings using INSERT/SELECT.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources