question

What decimal separator is set in "Regional and language options" for your windows account? Does it differ between your "system account" (or your service account for SQL Server) and your windows account?

In Sweden we use , and not . as decimal separator but most SQL servers are insalled under an account with . as the decimal separator and that causes problems sometimes, like the one you have in Excel.

What does SSIS indicate for the data types for the source and destination columns? If the source data type is wrong, try using a Data Conversion task. If the destination data type is wrong, try formatting the column in the spreadsheet appropriately.

Right-click on the Excel Destination, and select "Show Advanced Editor". Change to the "Input and Output Properties" tab. Expand the "Excel Destination Input" and "External Columns". Click on the "Premium" column. The Data Type is probably "Unicode string [DT_WSTR]". Change it to an appropriate numeric type.

I have not tried to write data to Excel using SSIS, but I had a similar problem using MS Access and VBA. The problem turned out to be the way Excel itself handles data types. It has some built in logic for "guessing" the datatype based on a sample size. If you have a lot of null values early in the output set it may interpret it as character data. There is a registry parameter named MAXSCANROWS that controls this behavior.

There is also an import mode on many connection strings called IMEX that is related to data typing.

This can be a frustrating thing to deal with because it depends on your actual data and the order that it is received. Setting MAXSCANSROW=0 can force it to evaluate all rows, but it may have performance considerations.

I suggest that you look at this link to see if it fits your situation. If it does not fit exactly, it may get you on the right track.

How To Use ADO with Excel Data from Visual Basic or VBA

http://support.microsoft.com/kb/257819

This worked for me.

From MSDN: " The Excel driver reads a certain number of rows (by default, 8 rows) in the specified source to guess at the data type of each column. When a column appears to contain mixed data types, especially numeric data mixed with text data, the driver decides in favor of the majority data type, and returns null values for cells that contain data of the other type. (In a tie, the numeric type wins.) Most cell formatting options in the Excel worksheet do not seem to affect this data type determination. You can modify this behavior of the Excel driver by specifying Import Mode. To specify Import Mode, add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window. " http://msdn.microsoft.com/en-us/library/ms141683.aspx http://support.microsoft.com/?scid=kb;en-us;194124&x=9&y=12

Andrea

Hi... i had the same problem... In my ETL my source is an Excel and has a column that first show a numbers but in last cells have a varchar data type..so when i import this infomration in a table the varchar type appear like a NULL i put de Imex = 1 but in my case this solucion is not appropiated for me.. do you known a different fort for de varchar doesn't appear like NULL