Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2010
    Posts
    1

    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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •