Bulk Insert Bug?

Dear Anyone,
I had been using BI Accelerator 1.1 for 2 years now. In
our current project, I encountered the following error
during the importing phase of BI.
I traced the problem to the bulk insert statement that BI
Accelerator is executing which is ...
BULK INSERT [Project88_Staging]..[S_Fact_BrdcastAlert]
FROM 'C:\Program Files\Microsoft SQL Server Accelerator
for BI\data\Project88\Import\S_Fact_BrdcastAlert.txt'
WITH ( FORMATFILE = 'C:\Program Files\Microsoft SQL
Server Accelerator for BI\data\Project88
\DTS\Import\Format\S_Fact_BrdcastAlert.fmt' ,
DATAFILETYPE = 'widechar', BATCHSIZE = 100000 ,
MAXERRORS = 100 )
The error that it generates is as follows:
Bulk Insert: DataFileType was incorrectly specified as
widechar. DataFileType will be assumed to be char because
the data file does not have a Unicode signature.
We export data to text files that BI Accelerator picks
up. All other files imports properly imported without
problems except for one fact table text file. We use the
same format file that BI Accelerator uses to export to
file the data to make sure that there would be no
incompatabilities. File size is about 2.3GB++, which is a
standard size with our other fact table files that doesnt
have any errors.
We also checked for data error which we found none. We
also checked the txt file we generate, notepad confirms
that it is a unicode file.
Can anyone please shed a light in what is happening. Is
this a bug? As much as possible I dont want to place a
workaround on this one since our entire framework for
loading data is all the same and has already been set up.
Any help would be apreciated.
Thank You.I'm seeing the same problem.
It seems that DTS is assuming we made a mistake in specifying
"wideChar" when it doesn't see the BOM in the Unicode file. BOM being
the first two bytes of the file being FF FE or FE FF indicating the
byte order of the file.
My problem is I'm exporting the data using SQL Server's bcp as Wide
Char data and bcp doesn't put the BOM in the file. bcp will import the
data if you tell it that it is WideChar data however.
Does anyone know how to get bcp to include the BOM or get DTS to trust
me that the data is in fact Little-Endian Unicode data?
Thanks,
Jim|||Joseph,
Can you look at the file in a hex editor to see if the error message
is true? Does the file have FFFE or FFFF as its first two bytes? If it
does not, can you post the bcp statement or otherwise indicate how you
exported the data to the file? Can you also post the format file you're
using? If you're getting this error and the file does indeed have FFFE
or FFFF at the beginning, I'll try to see what else I can find.
Steve Kass
Drew University
Joseph Ollero wrote:
>Dear Anyone,
>I had been using BI Accelerator 1.1 for 2 years now. In
>our current project, I encountered the following error
>during the importing phase of BI.
>I traced the problem to the bulk insert statement that BI
>Accelerator is executing which is ...
>BULK INSERT [Project88_Staging]..[S_Fact_BrdcastAlert]
>FROM 'C:\Program Files\Microsoft SQL Server Accelerator
>for BI\data\Project88\Import\S_Fact_BrdcastAlert.txt'
>WITH ( FORMATFILE = 'C:\Program Files\Microsoft SQL
>Server Accelerator for BI\data\Project88
>\DTS\Import\Format\S_Fact_BrdcastAlert.fmt' ,
>DATAFILETYPE = 'widechar', BATCHSIZE = 100000 ,
>MAXERRORS = 100 )
>
>The error that it generates is as follows:
>Bulk Insert: DataFileType was incorrectly specified as
>widechar. DataFileType will be assumed to be char because
>the data file does not have a Unicode signature.
>
>We export data to text files that BI Accelerator picks
>up. All other files imports properly imported without
>problems except for one fact table text file. We use the
>same format file that BI Accelerator uses to export to
>file the data to make sure that there would be no
>incompatabilities. File size is about 2.3GB++, which is a
>standard size with our other fact table files that doesnt
>have any errors.
>We also checked for data error which we found none. We
>also checked the txt file we generate, notepad confirms
>that it is a unicode file.
>Can anyone please shed a light in what is happening. Is
>this a bug? As much as possible I dont want to place a
>workaround on this one since our entire framework for
>loading data is all the same and has already been set up.
>Any help would be apreciated.
>
>Thank You.
>
>|||Jim,
Can you provide a bcp statement where the FFFE or FEFF isn't
specified? This shouldn't happen, and doesn't with the simple examples
I've tried.
If you don't have any choice but to work with a bad Unicode file
(missing the BOM), you can add the BOM yourself. The Cygwin Unix shell
for Windows contains an excellent set of utilities for text file
processing, and I'm sure there are good hex editors that can do this.
Steve Kass
Drew University
Jim O'Donnell wrote:
>I'm seeing the same problem.
>It seems that DTS is assuming we made a mistake in specifying
>"wideChar" when it doesn't see the BOM in the Unicode file. BOM being
>the first two bytes of the file being FF FE or FE FF indicating the
>byte order of the file.
>My problem is I'm exporting the data using SQL Server's bcp as Wide
>Char data and bcp doesn't put the BOM in the file. bcp will import the
>data if you tell it that it is WideChar data however.
>Does anyone know how to get bcp to include the BOM or get DTS to trust
>me that the data is in fact Little-Endian Unicode data?
>Thanks,
>Jim
>|||I did use a hex editor, that's how I concluded that was the problem. I
inserted the missing FFFE to test the problem. Same file with the BOM
has no problem being imported using DTS.
The problem seems to be with bcp on two machines.
I am using SQLDMO.BulkCopy2 View.ExportData through VB6 to create the
WideChar file. (and, yes, I did set .ExportWideChar = True)
I used command line bcp to test though:
bcp "DB.dbo.View" out "C:\bcpCmdTest.txt" -V"sa" -P"*****"
-S"ServerName" -w -t"|~" -r"\r\n"
Both with SQLDMO and command line bcp the files had no BOM
I just tried it on a third machine. The command line seems to create
the file with the BOM but the SQLDMO.BulkCopy2 still does it without
the BOM.|||Jim,
Any chance you have something like bcp.SetCodePage (SQLDMOBCP RAW) in
your code? The RAW specification will override the -w switch, by
implying that the data is not text, but native binary format. The
thread is here:
http://groups.google.com/groups?threadm=03a201c25dd5%240e6013f0%242ae2c90a%40phx.gbl
SK
Jim O'Donnell wrote:
>I did use a hex editor, that's how I concluded that was the problem. I
>inserted the missing FFFE to test the problem. Same file with the BOM
>has no problem being imported using DTS.
>The problem seems to be with bcp on two machines.
>I am using SQLDMO.BulkCopy2 View.ExportData through VB6 to create the
>WideChar file. (and, yes, I did set .ExportWideChar = True)
>I used command line bcp to test though:
>bcp "DB.dbo.View" out "C:\bcpCmdTest.txt" -V"sa" -P"*****"
>-S"ServerName" -w -t"|~" -r"\r\n"
>Both with SQLDMO and command line bcp the files had no BOM
>I just tried it on a third machine. The command line seems to create
>the file with the BOM but the SQLDMO.BulkCopy2 still does it without
>the BOM.
>|||Nope, this is the set up of the BulkCopy2 object:
Dim objBCP As New SQLDMO.BulkCopy2
.
.
With objBCP
.DataFilePath = FileName
'Set ExportWideChar to True for Unicode data file
.ExportWideChar = True
'Set up Column and Row delimeters for file
.ColumnDelimiter = ColumnDelim
.RowDelimiter = vbCrLf
.DataFileType = SQLDMODataFile_SpecialDelimitedChar
End With
.
.
(ColumnDelim is a string parameter of the function)
Thanks for your attention.
Jim