Search code examples
sql-serverexcelssisetlsql-server-data-tools

Workaround for exporting data to Excel with more than 255 columns

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?

Solution

  • 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