Changing DataTypes in an Excel Data Source

Hello.

I'm importing some data from an excel file to sql server 2005.

I created an Excel Data Source inside my Data Flow Task but it is assuming that the source columns DataType is double-precision float [DT_R8]. It isn't, even though some rows may containg numeric string in the column's cell.

If I go to the Data Sources advanded editor, and modify the data type property of the column, SSIS complains the the error output and the source output are not of the same DataType. If I try to change the error output's data type in the advanced editor I get this error: "Property Value". The deailed error states:

Error at MOVIM 04 [MOVIM 04 [1]]: The data type for "output "Excel Source Error Output" (10)" cannot be modified in the error "output column "Agente Protector" (7662)".

Error at MOVIM 04 [MOVIM 04 [1]]: Failed to set property "DataType" on "output column "Agente Protector" (7662)".

If i let SSIS correct the error by itself, it changed the dource column back to double-precision float [DT_R8].

Is there any way to get arround this?

Thanks in advance,

Hugo Oliveira

Hi,

By default SSIS will consider first 8 rows to determine the data type of each column. Refer http://support.microsoft.com/kb/189897/en-us regarding this. U can add "IMEX=1; MAXROWSTOSCAN=0" to your excel connection string to get around the problem. Hope it will work.

|||

Hello.

I've tried your tip and it's wotking well now.

Thanks,

Hugo Oliveira

|||

Adding IMEX=1 to the connection string made the following Error message to appear.

"Coulnd not find Installable ISAM"

I've been to http://support.microsoft.com/kb/209805 and the path in the registry key is correct has they say in the article.

Did anyone had the same problem ?

|||OK, IMEX=1 should be added to the extended properties of the Connection String.|||

Hi Thiru_ and Hugo

I added IMEX=1 in connection string!

When i have a column with simple data, for example:

A column with integer and string values it functioned correctly, but if will have columns with differents formatted cells the IMEX parameter doesn't function returning one data type default.

Some idea for this problem?

Thanks!

Andr Rentes
Brazil

PS. Hugo você brasileiro? Se for entre em contato para trocar idias sobre o SSIS, n?o achei nenhum fórum brasileiro sobre o mesmo. Meu email rentes @. gmail.com

|||

Hi,

I have a problem similar to this one. My excel contains data of the "general" type, mixing in the same column data that are by nature chars and ints: ex: 1, 2, ..., "5+". The automatic indentation of excel shows that implicitly the "5+" is treated as a char and the rest as numbers.

Depending on the first value and the IMEX setting, I can make SSIS consider the values of one of these 2 datatypes. When I use DT_NUMERIC I lose the "5+" which seems logical. But when I convert everything to char, even unicode DT_WSTR, I would have excpected that BOTH the numeric and string values are converted. But then I only read "5+" and not the rest.

I don't find a way either to read a column in twice, once with one data type and once with another.

Does anybody no a way around this?

Many thanks,

Jan