Autofit Column Width Using Ssis Etl On Visual Studio

I´m creating an ETL on visual studio, but when I export my data to a table on Excel, the columns seem a bit short. Is there a way to autofit the column width from visual studio? T

Solution 1:

You can achieve this using a script task that execute after the DataFlow Task success. You have to add Microsoft.Office.Interop.Excel assembly to the script task references and use the following code:(used Vb.Net)

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

PublicSub Main()

    Dim strFile asString = "C:\New Folder\1.xls"Dim m_XlApp AsNew Excel.Application
    Dim m_xlWrkbs As Excel.Workbooks = m_XlApp.Workbooks
    Dim m_xlWrkb As Excel.Workbook

    m_xlWrkb = m_xlWrkbs.Open(strFile)

    'Loop over all worksheetsForEach m_XlWrkSheet As Excel.Worksheet In m_xlWrkb.Worksheets

        'Columns    
        m_XlWrkSheet.UsedRange.Columns.AutoFit()

        'Rows
        m_XlWrkSheet.UsedRange.Rows.AutoFit()


    Next

    m_xlWrkb.Save()
    m_xlWrkb.Close(SaveChanges:=True)
    m_XlApp.Quit()

EndSub