question

Ron Steckly avatar image
Ron Steckly asked

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

ssisexcel
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Gustavo avatar image
Gustavo answered

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.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image
Tom Staab answered

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!

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.