Q: How to use SSIS script task to delete second row in Excel destination? SQL Server 2012 I have a simple package that extracts data from SQL Server and writes it to Excel spreadsheet.
Why am I doing this: the problem is that the value of one of the column is greater than 255 characters, which causes an error. I already tried different ways to find a solution, but so far without success. 
So I created dummy row in my Excel template that is able to accept more than 255 characters. After I load the data, I need to delete that dummy row. 
I do not know much about C#. How can I delete second row in Excel destination by using SSIS script task C#? Do I need to create variables that hold the path folder and file name?
And then how can I map those variables to my C# code?



A: You can use Microsoft.Office.Interop.Excel.dll library inside the Script Task to achieve this.
You can use a similar code:
Note: I assumed that the file path variable name is FilePath. Remember to select the FilePath variable in the Script Task ReadOnly Variables (in the Script editor). Also don't forget to add Microsoft.Office.Interop.Excel.dll as a reference inside the script task
Imports Microsoft.Office.Interop

Public Sub Main()

    Dim strFile as String = Dts.Variables("FilePath").Value 
    Dim m_XlApp = New Excel.Application
    m_XlApp.visible = False
    m_XlApp.DisplayAlerts = False
    Dim m_xlWrkbs As Excel.Workbooks = m_XlApp.Workbooks
    Dim m_xlWrkb As Excel.Workbook

    m_xlWrkb = m_xlWrkbs.Open(strFile)
    m_xlWrkb.DoNotPromptForConvert = true 

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

    m_XlWrkSheet.Cells(2, 1).EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp)

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

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

End Sub


A: I got the same damn problem.
Like you, I created dummy row in my Excel template that is able to accept more than 255 characters.
However, I works on Visual Studio 2008 and I can't install anythings on my computer.
My solution to delete the dummy row  is to set in it's first column the value "DummyRow" and create this following macro in the Excel template:
Private Sub Workbook_Open()
    Call SupprDummyRow
End Sub

Sub SupprDummyRow()

    x = Range("A2").Value 'First row after the titles, first column 
    y = Range("A3").Value 'Second row after the titles, first column

    If (x = "DummyRow" And y <> "") Then 'The Dummy Row is present AND the next row have value (we are in the export file)
            Range("A2").EntireRow.Delete 'Delete the lign
    End If
End Sub

Note that on my situation the first column have always a value (no empty string).
Hope this help!
Best regards,

A: Try this to increase the column length on the Excel destination. This way you would not even need to add your dummy record to the excel file and later delete it.


*

*Right Click on your Excel Destination.

*Click on "Show Advanced Editor".

*In the Advanced Editor, go to the "Input and Output Properties" tab.

*Expand the "External Column" of you Excel Destination Section and adjust the Length property values of the required column. You can also change data-types if needed. Please find the screenshot for the same.



Try this and let me know if it worked.

A: I created dummy row in my Excel template Which is able to accept more than 255 characters. After export data to excel file, delete dummy row from excel with SSIS scripts task.
Pass the exel file as variable like below immage

Write below code to delete 2 number row.  worksheet.Rows[2].Delete();

