I was able to resolve the error by changing the Cell Format in Excel worksheet from “Number” to “Text”. I got excited when I read the post by James to edit the connection string to have extended properties: Extended Properties="IMEX=1". Ultimately I found that data was converted to null ( Example: from "06" to "NULL"), and I found this via Preview in the source file connection ( Excel Source> Edit> Connection Manager> Sheet='MySheet'> Preview.). It took me a bit to realize the source of the error in my package. Obviously this method only works where you have header fields, but hopefully this helps some of you.
![excel text import wizard programmatically excel text import wizard programmatically](https://i.stack.imgur.com/0VXI3.png)
Using the advanced editor for the OLE DB source, set the outputĬolumn for the price field (or any other numeric field) to a numericĭataType. Prevent the header row from making it into your output by changing the SQL WHERE clause to exclude the header values e.g.Your initial problem is solved because your fields are now text, but you still have a header row in your output.SSIS to see all fields as text, including the price fields. Change your SQL statement to include the header fields.Our source files typically contain field headers (text) and prices (numeric fields), which gives me an easy solution:
EXCEL TEXT IMPORT WIZARD PROGRAMMATICALLY DRIVER
The problem is that even if you set your external input column to the desired data type, the file gets scanned every time you run the package and is dynamically changed to whatever the OLEDB driver thinks the field should be.
![excel text import wizard programmatically excel text import wizard programmatically](https://i.stack.imgur.com/Tsjg8.png)
Apparently someone doesn't think 12345 can be represented as a string. Instead of not importing character data, it stopped importing purely numeric data. I sorted my data so that mixed data types would be at the top, and guess what: The problem reversed. PRB: DTS Wizard may not detect Excel column type for mixed data in SQL Server.Why is SSIS insisting that these columns are Double? How can I force it to realize these are strings? Why does everything from microsoft have to not quite work correctly? If I try to edit the SSIS package and change the column types in the Excel Source, it won't let me change the type of the columns in the Error Output and gives me an error if the regular output and error output columns don't match.
![excel text import wizard programmatically excel text import wizard programmatically](https://provider.www.upenn.edu/computing/da/bo/webi/graphics/csvLeadingZeros.png)
I've tried remapping the columns to be nvarchar(255) but it still doesn't want to select the data it thinks is double, because there are characters in it. For some reason SSIS wants to believe two of the columns are of type Double, when they contain character data. I'm trying to import a spreadsheet to our database using SSIS.