none
Export 344 Columns data to Excel via SSIS

    Dotaz

  • Guys,
        
           For our project we try to export 344 columns data to Excel destination XLSX (2007 to 2010) through data flow task but we end up with error "too many columns defined".

    Is there any workaround to fix this issues  through script task C# code or any other way.  Please help me out on this issue. 

    CREATE TABLE `OLE DB Destination` (
        `Column 0` NVARCHAR(50),
        `Column 1` NVARCHAR(50),    
        `Column 2` NVARCHAR(50),
       .............................
    `Column 344` NVARCHAR(50)
    ) too many columns defined error

      


    • Upravený Mathan R středa 16. května 2018 14:19
    středa 16. května 2018 14:17

Odpovědi

  • Hi Mathan R,

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

    You can do some workaround to achieve this:

    1.Create a data flow task that export your data into a Flat File (csv)
    2.Store your Destination File Name in a Variable
    3.Create another Data flow task that convert your csv file to an Excel File using a script task with a similar Function.

       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

    4. The third party components.

    Reference: Workaround for exporting data to Excel with more than 255 columns

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Označen jako odpověď Mathan R čtvrtek 17. května 2018 10:05
    čtvrtek 17. května 2018 2:09
    Moderátor

Všechny reakce