Loss of data from Excel to SQL via DTS

This is a problem of the DTS reading the Excel with "estimating" the data type using the first (x) rows only.
As the rows start with numerical data, it will assume numerical all the time, and put NULL when it find non-numerical data.
I will try to find back the article that describes how to solve this...

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q257/8/19.asp&NoWebContent=1

Considerations That Apply to Both OLE DB Providers
A Caution about Mixed Data Types

As stated previously, ADO must guess at the data type for each column in your Excel worksheet or range. (This is not affected by Excel cell formatting settings.) A serious problem can arise if you have numeric values mixed with text values in the same column. Both the Jet and the ODBC Provider return the data of the majority type, but return NULL (empty) values for the minority data type. If the two types are equally mixed in the column, the provider chooses numeric over text.

For example:
•    In your eight (8) scanned rows, if the column contains five (5) numeric values and three (3) text values, the provider returns five (5) numbers and three (3) null values.
•    In your eight (8) scanned rows, if the column contains three (3) numeric values and five (5) text values, the provider returns three (3) null values and five (5) text values.
•    In your eight (8) scanned rows, if the column contains four (4) numeric values and four (4) text values, the provider returns four (4) numbers and four (4) null values.
As a result, if your column contains mixed values, your only recourse is to store numeric values in that column as text, and to convert them back to numbers when needed in the client application by using the Visual Basic VAL function or an equivalent.

To work around this problem for read-only data, enable Import Mode by using the setting "IMEX=1" in the Extended Properties section of the connection string. This enforces the ImportMixedTypes=Text registry setting. However, note that updates may give unexpected results in this mode. For additional information about this setting, click the article number below to view the article in the Microsoft Knowledge Base:
194124 (http://support.microsoft.com/kb/194124/EN-US/) PRB: Excel Values Returned as NULL Using DAO OpenRecordset

http://support.microsoft.com/kb/194124/EN-US/

create a tempory table in sql server and insert the contents from excel to this temporary table

field type should be varchar()

after this

the next transformation should be from this temporary file to your table. It will work

Thanks but I want the whole package to run via DTS so the import to the temporary table will include the nulls also.

did you try out my comment?

angelIII. I will try this today.

Okay. I tried very hard to do as you suggsested, resetting the connection string, but could not find enough information on how to do it. I am a novice with DTS and was unable to do this. If you have the time you may wish to explain how it can be done. To resolve the issue though I created a simple macro to run on the excel spreadshhet which places all SEDOL number containing a letter into a second worksheet and then running two separate transformations in one DTS package.
Thanks
Roger