January 31, 2008 at 8:02 am
Hi all,
I've been working with SSIS 2005, to import a user created Excel file into a table. The problem That I've been trying to resolve for sometime now is that there's a column in the Excel file that contains 2 types of data (numeric and String) and since the first 8 rows (by default) of that column are numeric, it decides the data type to DT_R8 instead of DT_WSTR... After searching for many days, I found that in the connection string I must put IMEX=1, and have the first 8 rows of that column to be strings so that the DTSX sees the column as a DT_WSTR... (if the first 8 rows are numeric, anything that is non numeric will be imported with a null value)
The only way I could think of (since I can't rely on the users), is to sort the file in descending order so that the 8 first rows can contain the alphanumeric values...
Finally the question, since I already have a SP created that goes and gets the file from an FTP site and do other things, How can I sort the Excel file before passing through the DTSX?
This way, I know it would work since I tried after sorting it manually.
If you need any more info, please don't hesitate to reply...
Thanks
John
January 31, 2008 at 9:48 am
You can tell SSIS which data type to use for output. It doesnt have to automatically choose. You can right click the Excel Source --> show advanced editor --> input and output properites --> expand output columns and change the column to a DT_WSTR.
January 31, 2008 at 9:59 am
I'm Pretty sure I tried that but I'll try to make sure... meanwhile here's the article I found regarding this error:
January 31, 2008 at 10:11 am
This link is for DAO OpenRecordsets. When you drop a Excel source in a dataflow task the connection string is OLE DB. Are you using the Excel source connector within a dataflow task?
January 31, 2008 at 10:21 am
Still doesn't work... It's still doing the samething (bringing in numeric values only - Non Numeric Values are null...
I have to sort the Excel file within the SP before sending it through the DTSX.
this is where I'm at now...
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=E:\Feed\BO_Data\DDC_Top_Sellers.xls;Extended Properties=Excel 8.0;IMEX=1')...[TopSellers$]
order by 2 desc
just to see if this would work... but getting the following error
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".
... and fighting to find a solution... 🙂
January 31, 2008 at 10:30 am
I'm using the Excel source Icon... that uses the following connection string...
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Feed\BO_Data\DDC_Top_Sellers.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";
It might be that the article is for ADO connections but the problem and solution reflects my exact problem and the IMEX=1 solution fixed my problem. Also when I sort my Excel file to have alphanumeric in the top 8 rows, it works... I don't know if they're aware that this problem also exists for OLEDB connections... but the solution works for OLEDB too... I'm 100% sure... this problem has been in my tasks for the past month (I've been working on and off this forst atleast a week)...
thanks
John
January 31, 2008 at 10:57 am
I know you can change the data type. Have your tried changing both the "External Column" data type and the output column data type? You can use the instruction I gave you for this. I just did a test where I made an Excel spreadsheet with a column that contain numbers for the first half of the the file then text for the remainder. The column was picked up by SSIS as a float. I changed the External and output columns and everything processed smoothly.
This is no reason you have to sort the data to make this work.
January 31, 2008 at 11:12 am
I just did a test where I made an Excel spreadsheet with a column that contain numbers for the first half of the the file then text for the remainder. The column was picked up by SSIS as a float. I changed the External and output columns and everything processed smoothly
This actually did not work. Everything processed correctly but when I checked the destination I was missing data, and I recieve errors in trying to change the format in the "External Column."
Interesting..:unsure:
January 31, 2008 at 11:25 am
Exactly... when you preview th data it seems fine... but when sending it to the destination it bugs...
Do me a favor and sort the excel spreadsheet before passing it thourgh the dtsx (having the 8 first rows alphanumeric)... that should also bug...
Then in the Excel Connection manager add the IMEX=1 (as stated in the article) also having the first 8 rows with alphanumeric values... and this should work.
Let me know when you have a chance...
Thanks
JG
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply