|
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
(comments are locked)
|
|
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.
(comments are locked)
|
|
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!
(comments are locked)
|
