Hi all, I'm deploying a SSIS Package with a DataFlowTask. Inside this Task i get data from my local SQL-Server and, after a Data Conversion Task, i put this data into an Excel (97-2003) file using the "Excel Destination" Object. In the Data Conversion Task I converted some 'varchar(6)' DB-fields into 'double-precision-float [DT_R8]' but when I open the output xls file these columns show the error 'the number in this cell is formatted as text...'. Obviusly these data are numeric (i.e. 0) but saved into the DB as varchar(6). How can I 'force' these excel-column to have numeric-value inside? I already set "Numeric" into "Format Cells" Dialog Box but the error is still here.. Thanks for your help

Try fixing this by setting the connection string of the Excel Destination to include the ;IMEX=1; directive. Arthur My Blog

I suggest to configure an error output to see the values causing errors: 1- Double-click on your Data Conversion component to open Data Conversion Transformation Editor; 2- Click Configure Error Output. Then In the new window opened set the Error column to Redirect Row. Hit ok 2 times to go back to Data Flow canvas; 3- Add a new destination component to your package, and connect the red arrow coming out of Data Conversion transformation to it.Configure the new destination to get the rows causing errors together with their error code. This way you can inspect what records are causeing the problem and fix them up. Let me know if you need more help.http://thebipalace.wordpress.com

Hi Arthur and thanks for the reply.. Isn't IMEX=1 for forcing every value to be processed as "text"? And, using the "Excel Destination" object, where can I add this part (";IMEX=1;") to the connection string? It appears that i can only select the xls file to write to.. Thanks for your help

Hi SaeedB, i followed your advice but the file connected with the red arrow remains blank. In fact I don't think this is an "Error" but something strange happening with Excel. The DB-rows are all "printed" into the destination Excel file, but some columns, even if the type is [DT_R8], are exported as Text, generating the message explained above because values of those cell can be considered as "numbers" (0, 1, 3000 ecc..) Thanks for your help

IMEX=1 sets the Excel driver into import mode. If the numbers do still come up as text, try adding a data conversion task before the stream hits Excel and convert the needed column in it to DT_R8, this type of a variable the Excel must figure out to be saved as numeric.Arthur My Blog