SSIS - export data to excel with more than 255 columns

There are a lot of limitations when exporting data to an Excel File using SSIS.

One of them that it is not allowed that the excel worksheet contains more then 255 columns.

More excel limitations can be found in this MSDN article

To solve this issue we can do some workaround:

1. Create a DataFlow Task that export your data into a Flat File (csv, txt, ...)
2. Store your Destination FileName in a Variable
3. Create another Dataflow Task that convert your Flat File to an Excel File using a script task with a Function like the following

    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

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

or you can use some Third-Party Components like cozyroc+

This is a part of my Stackoverflow answer posted 19 Feb 2017