Sql-server – SSIS Excel export changes # to

sql serversql-server-2008sql-server-2008-r2ssisssis-2012

I am exporting an excel file through SSIS using excel connection manager. I am using SSIS 2008 version and the excel is .xlsx

CREATE TABLE `Sheet1` (
    `Date` DATETIME,
    `NMMO` NVARCHAR(30),
    `P1 #` NVARCHAR(30),
    `P2 #` NVARCHAR(30)
)

but when i see the excel sheet, it gets created but the # symbol is replaced by . I have used P1 '# but it does not make any difference. Is there any way i can retain the column names in excel as it is? Please Help

Best Answer

You can alter the table header using a script task:

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

using System;
using System.Collections.Generic;
using System.Linq;
using System.IO;
using System.Text;
using Microsoft.Office.Interop.Excel;

public void Main()  
    {  

        string filename = @"F:\ExcelLibrary\myexcel.xls";
        if (File.Exists(filename))
        {
            File.Delete(filename);
        }
        Microsoft.Office.Interop.Excel._Application oApp;
        Microsoft.Office.Interop.Excel._Worksheet oSheet;
        Microsoft.Office.Interop.Excel._Workbook oBook;
        oApp = new Microsoft.Office.Interop.Excel.Application();
        oBook = oApp.Workbooks.Add();
        oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oBook.Worksheets.get_Item(1);
        oSheet.Cells[1, 1] = "Date";
        oSheet.Cells[1, 2] = "NMMO";
        oSheet.Cells[1, 3] = "P1 #";
        oSheet.Cells[1, 4] = "P2 #";
        //Microsoft.Office.Interop.Excel.Range oRange = oSheet.Range["A1", "B3"];
        if (oApp.Application.Sheets.Count < 1)
        {
            oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oBook.Worksheets.Add();
        }
        else
        {
            oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oApp.Worksheets[1];
        }
        oBook.SaveAs(filename);
        oBook.Close();
        oApp.Quit();
        Dts.TaskResult = (int)ScriptResults.Success;
    }

Don't forget to set the Delay Validation property to True for DataFlow Task which contains the Excel Source.

References

Related Question