Workaround for exporting data to Excel with more t

问题:

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?

回答1:

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

• SSIS: Export more than 255 columns from SQL table to Exce
• Cozyroc website

Workaround

• convert csv to xlsx
• trying to use custom assembly with script task in SSIS 2008 - can't find correct version of GACUtil

回答2:

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.