Excel Source Typing issues

Question

Post reply

Excel Source Typing issues

rsteckly

Say Hey Kid

Points: 664
More actions
November 16, 2009 at 7:31 pm

#139450

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

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

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

I have a number of questions.

1. Are you using the Excel Source?

2. Have you set the datatypes of the source using the advanced editor?

3. What destination are you using OLEDB, SQL Server, or Recordset?

4. Are you writing to an existing table or creating a table to be written to each time?

CEWII

rsteckly Say Hey Kid Points: 664 More actions · Answer 2

I have a number of questions.

1. Are you using the Excel Source?

Yes, although I've tried converting the data to csv format and importing it using the flat file connector. I usually click "suggest types" and tell it to check the first 1000 rows. I still have the problem, however, of it not typing some columns correctly.

2. Have you set the datatypes of the source using the advanced editor?

Not sure what you mean here--I don't really see an advanced editor with the Excel Source. I have tried overriding the suggested values in the flat file connector for fields previous attempts have had trouble with.

3. What destination are you using OLEDB, SQL Server, or Recordset?

ADO.NET destination.

4. Are you writing to an existing table or creating a table to be written to each time?

I'm having it create a table.

Thanks!

Ron

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

rsteckly (11/17/2009)
I have a number of questions.
1. Are you using the Excel Source?
Yes, although I've tried converting the data to csv format and importing it using the flat file connector. I usually click "suggest types" and tell it to check the first 1000 rows. I still have the problem, however, of it not typing some columns correctly.
2. Have you set the datatypes of the source using the advanced editor?
Not sure what you mean here--I don't really see an advanced editor with the Excel Source. I have tried overriding the suggested values in the flat file connector for fields previous attempts have had trouble with.
3. What destination are you using OLEDB, SQL Server, or Recordset?
ADO.NET destination.
4. Are you writing to an existing table or creating a table to be written to each time?
I'm having it create a table.

I think you have a lot of variability here.

On the Excel source you can edit the underlying column definitions with the advanced editor I often do, if it complains later (With a warning NOT an error) I ignore it. The advanced editor is on the source itself not the connection manager. If you right click on the Excel Source it is an option. You need to force the types to be what length and type are correct.

The ADO.NET destination is a SQL 2008 SSIS item. Unfortunately I don't have that right now.. So I might have trouble offering anything on that..

I would create a table and just write to it, you then know exactly what structure it is.

BTW, what kind of database is the table sitting on?

CEWII

rsteckly Say Hey Kid Points: 664 More actions · Answer 4

Its sitting on a SQL Server 2008 table.

One thing that has helped is setting the On truncation to ignore rather than fail component. I looked through the data and ran some checks; I haven't noticed any truncated data.

I suspect it might *think* it is truncating when in fact it isn't. Does that seem like a reasonable hypothesis?

Ron

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

I haven't experienced it incorrectly thinking it is truncating when it isn't. You might try to do a redirect instead of ignore and add a dataviewer to the redirect pipeline to see what row it thinks is being truncated.

CEWII