Hi Experts, Can anybody tell me how to import data into SQL table from Excel column which has more than 255 characters? Thanks Regards, KumarKG, MCTS

Hello, Please have a look http://www.sql-server-performance.com/faq/Using_SSIS_Import_an_Excel_file_which_has_columns_with_moe_than_255_characters_p1.aspx and this one http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/a0720f40-6c47-47d6-99e5-f39851d4476b Thanks

Hi Amir, I was aware that somebody will only paste me links, but this links I have already tried and hased got any luck so posted my question to this forum. for your first link, when I go to :- HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft ---->> here I don't find "JET" into my machine, so I'm unable to go further. (IS THIS IS THE GOOD PRACTICE TO CHANGE THE REGISTRY) for your second link, I'm unable to follow their conversation I think they are jumping one issue to another and I cannot follow them, even I tried below link, http://www.justintubbs.com/code-samples/ssis-excel-source-failed-to-retrieve-long-data.php but again in vain. Can you share something were I can fix this issue? I'm screwing this since an an hour, but not getting more path. Please let me know if you need any questions for me. Thanks KumarKG, MCTS

I understand your frustration. Is Mr. Warraich on your payroll? He suggested some things that have been published that you can try. From your post, you have looked at these published resources, tried some and have problems with others. You have very likely discouraged someone who might have the answers you need by your demanding tone.Russel Loski, MCT

Hello Kumar, The reason you don't have JET tab, could be because you don't have Excel installed on machine and you might will not be able to do that as it is registry change and companies don't install Excel on Production Servers. Second solution can be to insert some data in column manually as excel read first 8 lines to decide about data type. So put some data in first or second row with length more than 255 in your source Excel and then you will be able to get the data. You can use some template with first row populate with more than 255 characters and then insert your source data, after reading neglect this first row by using conditional split in Data flow task. Thanks

Hi Aamir, I tried your first link, here is the path I found as 64-Bit:- Regedit-->HKEY_Local_Machine-->Software-->WOW6432Node-->Microsoft--Jet-->4.0-->Engines--Excel- Then you can change the TypeGuessRows from Hexadecimal 8 to Hexadeciman 3e8 (which is equal to Decimal 1000), now I looked into my column by going into "Advance Editor for Excel source" in BIDS, the Data Type for that column has been changed from "Unicode String (DW_WSTR)", length 255 to "Unicode text stream [DT_NTEXT]", length 0 for External columns, Output columns under Excel Source Output and also for Output columns under Excel source Error Output, now as my destination data-type for the column is NVARCHAR(1000) I kept Derived column for that column with the expression as:-(DT_WSTR,1000)[Column-Name] And below that i kept "OLEDB Destination" with mapping correct with the new column from the Derived column, but when I execute the package I see below error message:- [OLE DB Destination [293]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Any help? what needs to be change? Thanks KumarKG, MCTS

Hi Amir, fixed the issue as it was having multiple columns who has more than 255 characters. Thanks KumarKG, MCTS