sql server - Format excel destination column in ssis script task

Question

Is it possible to format a column in an excel destination in ssis before generating it? I'm thinking a script task? I want to format a column to be date/time format within the excel spreadsheet

Answer 1

You can use Microsoft.Interop.Excel library and use NumberFormat property to change EntireColumn format to datetime.

Note: you have to add Microsoft.Office.Interop.Excel.dll file to the following directories (.Net Framework dll directory) C:WindowsMicrosoft.NETFrameworkv2.0.50727 and (sql server data tools dll directory) C:Program FilesMicrosoft SQL Server100DTSBinn (if using vs 2005 and sql 2008) and then add this dll as a reference in your script task

Imports Microsoft.Interop.Excel

Public Sub Main()

        Dim m_XlApp = New Excel.Application
        Dim m_xlWrkbs As Excel.Workbooks = m_XlApp.Workbooks
        Dim m_xlWrkb As Excel.Workbook
        m_xlWrkb = m_xlWrkbs.Open("D:1.xlsx")

        Dim m_XlWrkSheet As Excel.Worksheet = m_xlWrkb.Worksheets(1)

        m_XlWrkSheet.Columns(1).NumberFormat = "HH:mm:ss"
        'OR
        'ExcelWorksheet.Cells(1,1).EntireColumn.NumberFormat = "HH:mm:ss"

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

        Marshal.ReleaseComObject(m_xlWrkb)
        Marshal.ReleaseComObject(m_xlWrkbs)
        m_XlApp.Quit()
        Marshal.ReleaseComObject(m_XlApp)


        Dts.TaskResult = ScriptResults.Success

End Sub

References

• Format an Excel column (or cell) as Text in C#? Look at all answers, not aonly the accepted one
• Interop.Excel - Set date format
• Range.NumberFormat Property