2

SSMS and SSRS to Excel enable for more than 255 columns when copy-pasting.

SSIS does not allow for more than 255 columns to be exported to Excel 2007. Is there a way to override this?

Hadi
  • 34,224
  • 11
  • 61
  • 121
dirtyw0lf
  • 1,697
  • 5
  • 27
  • 60

3 Answers3

2

Problem

There are a lot of Limitations when exporting to an Excel Files using Sql server data tools

Workarounds

You can do some workaround to achieve this:

  1. Create a dataflowtask that export your data into a FlatFile (csv)
  2. Store your Destination FileName in a Variable
  3. Create another Dataflowtask that convert your csv file to an Excel File using a script task with a similar Function

Note: you have to add Microsoft.Office.Interop.Excel.dll file to the following directories (.Net Framework dll directory) C:\Windows\Microsoft.NET\Framework\v2.0.50727 and (sql server data tools dll directory) C:\Program Files\Microsoft SQL Server\100\DTS\Binn (using vs 2005 and sql 2008) and then add this dll as a reference in your script task

    Imports Microsoft.Office.Interop

    Public Sub ConvertCSVToExcel(Fromcsv As String, Toxlsx As String)
        Dim Exl As New Excel.Application()
        Try
            Dim wb1 As Excel.Workbook = Exl.Workbooks.Open(Fromcsv, Format:=4)
            wb1.SaveAs(Toxlsx, FileFormat:=XlFileFormat.xlOpenXMLWorkbook)
            wb1.Close()
            Exl.Quit()
        Catch ex As Exception

                        Exl.DisplayAlerts = False
            Exl.Quit()

        End Try
    End Sub

Third party components

Or you have to use a third party components like cozyRoc SSIS+

Side Note

if you are looking to Import data from excel with more than 255 columns you can follow this Link

References

Third party components

Workaround

Hadi
  • 34,224
  • 11
  • 61
  • 121
1

Refer the link. Best would be to create an script in SSIS to copy the content as csv format. You can use c# or VB.Net.

Mukul Varshney
  • 3,111
  • 1
  • 10
  • 19
0

just for anyone come to this page and use SQL Server Import and Export Wizard to export to excel

in tab Review Data Type Mapping section Data Type Mapping: ==> double click your column that has length more than 255 char open its Column Conversion Details Dialog then close it then next to end...Lool

Ehsan Kenari
  • 81
  • 3