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?