Thread: ssis excel more that 400 columns
07-18-10, 12:36 #1Registered User
- Join Date
- Jul 2010
Unanswered: ssis excel more that 400 columns
I am trying to do a Sql To Excel Data Dumping with the help of ssis flow task.I create a sql connection and give the query in the sql source.I aslo have a excel template to map in excel destination ready which has around 400 columns/fields.In bettween i use a dataconvrsion for unicode/non unicode issue.Now when i run this package i get errors like."excel metadata in nvalid","error creating oledb accessor".....is it due to any limitations dat we cannot populate more that 255 cols/fields in excel(i am using 2003 excel).
Please help me with a suggestion
07-18-10, 17:29 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
The XLS file format has a limit of 255 columns, there isn't a way to change that.
If you can switch to Excel 2007 or Excel 2010 you could then use the XLSX file format, but that will require you to configure both your SSIS server and your client workstations with the newer version of Excel.
If you have less than 255 rows of data, you might consider a pivot to swap the rows for columns. This might be an option to allow you to use the XLS file format.
If you can switch to another file format like Comma Separated Values (CSV), Tab Delimited (TXT), or another file format you should be Ok too. They don't have the 255 column limit.
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.