3

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

  • 2
    Do you have to create the sheets in SSIS? I've done it where I make a 'template' of the Excel file I want, and then SSIS makes a copy of that file, and dumps the data into a blank tab that is hidden. The primary tab, 'Sheet1' in your case, would be =A1, =B1, etc. Essentially replicating the results to Sheet1 from the sheet where the results were dumped. This allows me to keep formatting, formulas, graphs, etc. – Shaulinator Dec 7 '17 at 14:06
  • I have to create the sheet on runtime and when the package runs, cannot use copy option. as we dont have other location – aman6496 Dec 7 '17 at 14:41
  • You can't create a subfolder in your current location called Template, store the template Excel doc there, and copy the template file to the main folder in the SSIS package? – RDFozz Dec 7 '17 at 21:59
  • Can we use a script task to rename a column in excel sheet after the file is created. there is a datetime stamp in excel sheet created. – aman6496 Dec 11 '17 at 9:06
  • @aman6496 have you tried my suggestion, i provided a script task code that can achieve this!! – Hadi Jan 15 '18 at 21:27
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.