x

Excel Data Source Column Typing Issue

Hi,

I have an Excel worksheet that I'm reading into a table. This table is created by the ADO.NET destination adapter.

The trouble is that sometimes the ADO.NET destination adapter creates a string field for a field that was meant to be numeric.

This becomes a problem when I go to make aggregations because some of these fields that have been mistyped as strings I group by.

When I add a data conversion task to the data flow to convert these strings to numbers, the package fails because it says the package was stopped to prevent loss of data.

I've tried reading it in as a flat file (csv) and having INtegration services suggest types; this hasn't worked either because it still mistypes some things as strings and complains again about data truncation when I go to convert the data.

Anybody have suggestions?

Thanks,

Ron

more ▼

asked Nov 16 '09 at 11:33 PM in Default

Ron Steckly gravatar image

Ron Steckly
21 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

While reading from external sources, SQL Server and most drivers tries to guess the column type by taking a small sample of data, so if your initial 200 lines only have numbers it will be stored in a numeric field even if later on the file appears a string column.

If thats what is causing your problem, than you can try to force the order of the columns ( alfa-numeric first), include a sample row with the correct formats in the initial of the file, import everyone as strings and work with data types later or have a destination table that is not automatically created.

Let us know if this helps.

more ▼

answered Nov 18 '09 at 08:16 AM

Gustavo gravatar image

Gustavo
592 4 4 7

(comments are locked)
10|1200 characters needed characters left

One possibility could be if someone actually stored $ or other characters in the cell. It's hard to say without seeing the data, but I have a suggestion for a way to possibly narrow down the problem.

Create an exceptions table with all varchar fields of sufficient width. Then redirect all errors (truncation and other) to the new table. Run the data flow and check the rows in the exceptions table. If you're still not sure what the problem is, reply here with the results of this test. Good luck!

more ▼

answered Nov 17 '09 at 01:31 AM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x899
x108

asked: Nov 16 '09 at 11:33 PM

Seen: 2006 times

Last Updated: Nov 16 '09 at 11:33 PM