I am trying to bulk insert data from a fixed-width text file, one column of
which contains a char(9), using a copy column transform via a standard
transform data pump task. This generally runs with no problems, however the
vast majority of the data is numeric, and in the event one row contains an
alpha char in this char(9), the following error is generated:

Syntax error converting the varchar value 'ABBBBBBBB' to a column of data
type int.

My best guess at what is happening here is that, at the beginning of each
bulk insert operation (say my bulk insert size is 200), DTS scans the first
10 or 20 rows to determine the input data type. Because the majority of my
data is numeric, it tries to bulk insert this data specifying a numeric type
and fails.

I just want to insert the ABBBBBBBB and continue. But I'm at wit's end
trying to figure out how to do it.

Looking at connection properties in the Disconnected Edit dialog, there is a
field "Column Metadata Array" --the name suggests it may provide a way to
override this behavior. Is there any documentation available for this
property?

All fields in a text file are charchter fields.
You have a definition set up for the destination.

What the error is telling you is that there is a mapping between an
attribute in the source (character) and an attribute in the destination
(int). That in itself is not a problem. There will be an implicit
conversion attempt on the data from the source. In your case though
you have charcter data in that source field that cannot be converted
i.e. AAABBB.



Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

The destination column definition is char(9), not int. Nowhere is int
specified, and if I place alpha chars in this field in the first few rows of
the data file, those rows are inserted ok. The behavior suggests that during
a fastload BULK INSERT, DTS is autoconfiguring the source data type value
used in the based on what it thinks the data type is (SQLINT) for each batch
(99.9% of the source data is numeric, so it assumes the source column is
entirely numeric).

Using Format Files:

Data type stored in the particular field of the data file. With ASCII data
files, use SQLCHAR; for native format data files, use default data types.
For more information, see File Storage Type.

File Storage Type:

File storage type Host file data type
char SQLCHAR
varchar SQLCHAR
nchar SQLNCHAR
nvarchar SQLNCHAR
text SQLCHAR
ntext SQLNCHAR
binary SQLBINARY
varbinary SQLBINARY
image SQLBINARY
datetime SQLDATETIME
smalldatetime SQLDATETIM4
decimal SQLDECIMAL
numeric SQLNUMERIC
float SQLFLT8
real SQLFLT4
int SQLINT
bigint SQLBIGINT
smallint SQLSMALLINT
tinyint SQLTINYINT
money SQLMONEY
smallmoney SQLMONEY4
bit SQLBIT
uniqueidentifier SQLUNIQUEID
sql_variant SQLVARIANT
timestamp SQLBINARY


Thoughts?

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Datatype guessing is a feature of the XL driver. Even then though this
just results in NULL values being entered for data that does not meet
that criteria. I am a little confused by your problem though.

Can you show us a file?
Are you simply doing a 1:1 in a Data Transform Task?


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

Input data looks like this (17,000 rows)

001621409
008461819
013468129
038502183
040421644
040684765
040761566
040822084
040888238
041463919
041822020
042409192
042605417
042702359
042762927
043446946
043547649
044388726
044409004
044508681
044563638
044702011
044749159
045304993
045425327
045621329
045667350
045766740
045885223
046529845
046562957
046769910
047449737
047460243
047563607
047769241
048569101
049406143
049407998
049467989
049683402
049726466
049746894
060483592
063505864
127467250
156466400
196329445
301624793
367529700
519926763
581594426
600124409
.... (3000 rows)
AABBBBBBB
256806100
436900300
528870821
535588981
545952822
547531348
548198644
555952578
555953045
561979073
564254356
573020111
621508251

The destination is a SQL Server 2k char(9) column, with fastload on and bulk
size set to 1000. I am using straight copy column, but have tried every
other kind of transform possible (trim, substring, activex, uppercase), but
none seem to help (transform results are written to a buffer; the
problematic char->int conversion does not happen until the actual bulk
insert operation takes place).


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

OK Cool let me test.

The attribute should be evaluated as a string on the source and you are
going to a string destination. The problem as I can see is that you
think the driver is evaluating the attribute as an integer and reading
the [A-B][A-B][A-B][A-B][A-B][A-B][A-B][A-B][A-B] As being bad.

CREATE TABLE Dest(Attr1 Char(9))

My File is 8587 rows long and at row 3000 is our
[A-B][A-B][A-B][A-B][A-B][A-B][A-B][A-B][A-B]


I create a simple data transformation task between the text file and
the SQL Server table.

My sample works for me.

What SP and edition of DTS Are you using?




Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

In article <o%VGc.5153$sD4.1268 (AT) newsread3 (DOT) news.atl.earthlink.net>, Isak
Dinesen wrote:
Tried with the BULK INSERT task also and it worked.


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

Well I appreciate the sanity check. It only confirms what we all already
know about my sanity however... :-) My test case is not so simple in that
the source and destination table have 18 other columns, some of which are
dates, others ints, etc. but it won't hurt to try running a simpler test on
my end. I'll try that and report back my findings....


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Is there anyway that you have mapped a Source attribute to an INT
destination attribute? That can be the only explanation for me.



Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

You are right... The reason the error was sporadic (some alpha values
inserted successfully) is that there is a stored procedure that I am running
inside the Batch Complete phase to verify the last batch completed
successfully (DTS does not tell you whether the last batch completed
successfully inside this phase, so I wrote a stored proc to test whether the
last row of the last batch exists in the database). This row just happened
to be the last row in the batch, and my stored proc was set up to accept
that value in a parameter that was incorrectly typed int...

Thanks for your help!

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote