SQL Server 2005: Export to Access Screws up Datatypes

[jasonp45]

When I export from SQL Server 2005 using the wizard, I select SQL Native Client as source, then an Access 2000 database as target...when I output the data, all of my text fields are output as 'LongText' (i.e., memo-field), except for source fields of length 1 which seem to work (as do numeric fields).

I tried switching the output to OLEDB, and in tweaking the advanced properties (JetOLEDB:EngineType I believe) it found the correct field-lengths for my text fields, but didn't assign a datatype!

So in either case I must manually go through the entire list of fields and enter the datatype and/or length.

Surely there must be a way to coax DTS to correctly map text datatypes from SQL Server to Access??? Any help greatly appreciated.

 

[AlexCuse]

You want the SSIS forum forum1555

But with DTS I normally try to either script my table creation or keep a table in the access db ready to accept what I am sending out.

Hope this helps,

ALex

Ignorance of certain subjects is a great part of wisdom

 

[jasonp45]

Thanks - I've posted it over there.