I have a DTS package which transforms Excel spreadsheet data into
table data. The problem I'm having is that DTS fails to properly
parse Zip+4 entries. I specified the transformation as
"Column Copy" thinking a straight copy of that data would suffice.
For 5-digit zips it work correctly. However zip+4 entries
cause an error in DTS. Does anyone know a solution or work around to
this problem? Thanks.

Can you show me

Destination table structure?
A good example of data ?
A Failing example of data?
The error text?
What is Zip+4 ?

--

----------------------------
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



"Jeff Robinson" <jeff.robinson (AT) aquilent (DOT) com> wrote

Allan,

Zip+4 is the 9-digit zip code (e.g. 21045-1500). The DTS package
works flawlessly on 5-digit zip codes (e.g. 21045) but fails with the
following error on 9-digit codes:

Error: -2147213274 (80042026); Provider Error: 0 (0)
Error string: TransformCopy 'DTSTransformation__8' conversion error:
Destination does not allow NULL on column pair 1 (source column 'Zip'
(DBTYPE_R8), destination column 'PostalCode' (DBTYPE_WSTR)).
Error source: Microsoft Data Transformation Services (DTS) Data Pump
Help file: sqldts80.hlp
Help context: 30501

The table structure is as follows:

Company Name nvarchar(35)
Address1 nvarchar(255)
Address2 nvarchar(255)
City nvarchar(50)
StateorProvince nvarchar(50)
PostalCode nvarchar(20)

The data source is an Excel spreadsheet and all transformation are
"column copy". Thanks.


Jeff


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

OK I see it now

Here is what I think you need

Excel Inserts Null Values
(http://www.sqldts.com/default.aspx?254)

Datatypes are "Best Guessed" when using Excel so

12345 = numeric
12345-1234 = char

If you mix these then the Excel driver gets all wonky

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Jeff Robinson" <jeff.robinson (AT) aquilent (DOT) com> wrote