CODEWITHSUNDEEP
sql-serverexcelssisetlsql-server-data-tools
dirtyw0lf
dirtyw0lf

Reputation: 1958

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?

Upvotes: 2

Views: 4255

Answers (3)

Ehsan Kenari
Ehsan Kenari

Reputation: 131

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

Upvotes: 1

Hadi
Hadi

Reputation: 37313

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

Upvotes: 2

Mukul Varshney
Mukul Varshney

Reputation: 3141

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.

Upvotes: 1

Related Questions