Problems uploading data from excel to SQL 2000 (DTs). The DTS is taking the data like a numeric one.

Question

Problems uploading data from excel to SQL 2000 (DTs). The DTS is taking the data like a numeric one.

dvora.id

Mr or Mrs. 500

Points: 549
More actions
January 11, 2009 at 4:30 am

#73174

I’m trying to upload an excel file via DTS (SQL 2000).
In one of the columns I’m receiving codes, most of them are numeric and the rest nvarchart.
During the upload the dTS is getting only those code that they are numeric and the rest as null data.
I’d be glad if someone can help me to create an Activex scrip in order to transform this data to nvarchart while the transferring .
Thanks!
Deby

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

You'll have to provide more information about the table and what you are doing. What type of transfer are you doing, what is the table DDl, etc.

Also, there's no such thing as nvarchart. There are nvarchar and varchar.

happycat59 One Orange Chip Points: 29389 More actions · Answer 2

In my experience, this sort of issue happens because of the way the Jet OLEDB provider attempts to determine the datatype. What it does is examine the first "few" rows for each column and based on what it finds, it derives that datatype. If the first "few" columns happen to be numeric, then it uses one of the numeric (sorry - not sure which one). If it then encounters data that cannot be represented using that datatype, it returns nulls.

Now, you can influence how the Jet OLDB provider parses the data. One option is to use the extended properties on the connection string and include "IMEX=1". Have a look at http://www.connectionstrings.com/excel for more on this.

Another option is to update the registry on each workstation / server that runs the package. There is a value in there that tells the provider how many rows to parse when attempting to work out what datatype to use.

The key is HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows.

The version number may be different for you depending on what you have installed.

As usual, be careful when editting the registry - take a backup before you start.

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 3

Dvora

As happycat describe the issue this is a common problem importing Excel files using DTS.

What you can do is to save the Excelfile as a tab sep textfile and then import the file.

I do so instead of manipulating the register.

(Another way is to use VBA and ADO SQL insert)

Best Luck

Gosta

dvora.id Mr or Mrs. 500 Points: 549 More actions · Answer 4

thanks happycat59!

I tried it by doing right click on the board of the DTS and getting to the Disconnect Edit .

But, it wasn't help me.

Thanks! anyway.

dvora.id Mr or Mrs. 500 Points: 549 More actions · Answer 5

Gosta Munktell, i solved this problem by changing to the text file as you suggest.

Thanks!

Deby