|
|
Posted by Ted on 08/08/06 16:45
Thanks Erland,
To deal with the quotes (because your example doesn't show what happens
if the first column in the file is quoted text), I tried the data
import wizard from within SQL Server Management Studio. For most of
the data files/table combinations, it worked well. However, there are
issues, especially related to nulls.
1) With one file, some of which goes into one tabe and some goes into
another, the last column contains null values, and consequently the
load fails. The key error seems to be:
Error 0xc020901c: Data Flow Task: There was an error with input column
"Dist_Unit" (67) on input "Destination Input" (51). The column status
returned was: "The value could not be converted because of a potential
loss of data.".
(SQL Server Import and Export Wizard)
Dist_Unit is the last column and more often than not contains null
values.
This is especially puzzling since the wizard, when asked to show a
preview, properly displays the data without complaint.
2) With a different data file/table, there are two fields containing
integers, both of which could contain nulls, and while the wizard will
import the data without complaint, it silently converts the nulls to
zero. Is there a way to tell the wizard to keep the nulls as nulls?
If so, might this fix the problem in item #1?
3) I am trying to populate a lookup table from data used in item #1.
Of course, in that file, there will be multiple occurances of most
supplier code/supplier name pairs (one for each product supplied by the
supplier). This leads to the wizard complaining about violating the
primary key. Is there a way to tell the wizard to ignore duplicate
records?
4) Each time I tried the wizard, I told it to store a package on the
server. However, I can't seem to find these packages. Where should I
be looking for them, and can I tell SQL Server Management Studio to
export the packages as scripts I can invokve from the commandline?
Thanks
Ted
Navigation:
[Reply to this message]
|