Hi, I have to transform 500 columns from an excel sheet to Sql Server. In Excel 2k3 , I can read a max of 256 columns only. If I use Excel 2k7, then SSIS 2k5 excel source does not support excel 2k7. If I use ole db source then again it can read a max of 256 columns. Could anyone please suggest me how can we read 500 columns in excel sheet (Around 10000 rows) efficiently using SSIS 2k5. Raj

Preconvert them to csv with a VB script, then try loading as csv files. That way you can also use the 64bit runtime.My Blog "Karl Beran's BI Mumble"

Preconvert them to csv with a VB script, then try loading as csv files. My Blog "Karl Beran's BI Mumble" Agreed. Altho you don't even need to write any VB - any Excel file can be saved as a CSV (comma-seperated value) file. Importing .CSV files using SSIS is much easier than .xls files.http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Thanks Karl and Jamie. I'll transform my excel file into csv and try this approach. But I guess we can't have multiple sheets in CSV file, which is my case. I have some master data in other sheets too. Raj

Thanks Karl and Jamie. I'll try this approach and let you know. Raj

Hi, I have to transform 500 columns from an excel sheet to Sql Server. In Excel 2k3 , I can read a max of 256 columns only. If I use Excel 2k7, then SSIS 2k5 excel source does not support excel 2k7. If I use ole db source then again it can read a max of 256 columns. Could anyone please suggest me how can we read 500 columns in excel sheet (Around 10000 rows) efficiently using SSIS 2k5. Raj If you can use third-party solutions, check the commercial CozyRoc Excel Source Plus component. It supports Excel 97-2010 and doesn't have 256 columns limit.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Hi Raj, We can install the Microsoft Access Database Engine 2010, and then use the Microsft Office Acess Database Engine OLE DB provider in SQL Server Integration Services(SSIS) to restrieve data from Excel 2007. This don't have the 256 columns limited. Microsoft Access Database Engine 2010 can be downloaded from: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d If you have any more questions, please feel free to ask. Thanks, Jin Chen Jin Chen - MSFT