none
Debug Failure in SSIS ScriptTask when using Microsoft Excel Reference (interop) RRS feed

  • Question

  • Fellow SSISers

    I have moved some ssis 2008 packages to ssis 2016.

    These packages use Excel (Microsoft.Interop). This was necessary for the specific requirements by customer. Only way to really do what we needed.

    Now, I can absolutely confirm that I no longer can get breakpoints to work when using interop on ssis 2016.

    When I remove the reference for Excel (and the code inside that uses the reference). the breakpoint works.

    When I put it back, the breakpoint is ignored. Even if I just add the reference without the vb code, it still failes to breakpoint.

    This is in the main package NOT A CHILD PACKAGE.

    1 - Does anyone know if MS is working on a fix for this?

    2 - Other than putting messagebox.show in the code or other cluggy approaches, is there a workaround?

    Thanks,

    MG

    Friday, April 26, 2019 6:43 PM

Answers

  • Hi MG,

    Well, I can reproduce the problem exactly, and find some interesting things in the package file. 

    Before you edit the package file, please make a copy of the original package. 

    You need to remove the COMReference node(marked in following picture) from the package file(.dtsx), using text editor, like Notepad++. Then the breakpoints will work fine, with the new reference(Microsoft.Office.Interop.Excel.dll). 

    It seems that Script Task breakpoints can't work with COM reference, not only "Microsoft Excel 16.0 Object Library". But, even if the COM reference is deleted and replaced by the new reference, the package content is not updated. 


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by mg101 Tuesday, April 30, 2019 4:44 PM
    Tuesday, April 30, 2019 3:27 AM

All replies

  • Hi MG,

    1. Maybe the version/edition of Microsoft.Office.Interop.Excel is not available in SSIS 2016 package, check your excel version/edition, as well as the assembly you referenced.

    2. Try to capture the exception and write into file system, or raise event in the Script Task.

    Regards,

    Pirlo Zhang 

    Sunday, April 28, 2019 9:09 AM
    Moderator
  • Hi Pirlo,

    Thanks for your comments.

    The excel code runs fine. The package runs fine.

    The issue is the debugger. if I need to breakpoint and walk thru the code - I can not.

    MG

    Sunday, April 28, 2019 3:23 PM
  • Hi MG,

    Thanks for your feedback. 

    According to my test, it seems to be related to how the reference is add. 

    In Reference Manager, if you add the "Microsoft Excel 16.0 Object Library" directly in COM pane, the breakpoints will be ignored.

    If you add the reference by click "Browse" and select "Microsoft.Office.Interop.Excel.dll" in the following folder: 

    C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0__71e9bce111e9429c
    the breakpoints will work fine. 


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, April 29, 2019 3:29 AM
  • Hi Yang,

    I have done exactly that:

    1 - I removed my exising Excel and Office references in scrip task.

    2 - I have added reference EXACTLY what you indicated. Saved, script rebuilt, package rebuilt.

    Breakpoints will still not open the window.

    I tried both on the Windows Ever 2012 and also on a separate laptop. Both will not launch breakpoint.

    I also confirmed that inside the ssis CODE, that the reference is now pointing to 15.0.0.0.0

    What next?  Is there some patch that is needed?

    MG

    Monday, April 29, 2019 1:31 PM
  • Yang,

    I finally got your suggestion to work!!!!!

    But I observed a strange anomaly.

    If I had a script task that would NOT work with debug - already using the Excel 2016 assembly NOT in the folder you recommended. It would get stuck pointing to that. In other words. I would open the Script Task, remove the old Excel reference, add your new reference, save it. I would retype the IMPORTS Microsoft.Office.Interop.Excel. Nope - VB could not find it. Tried numerous times. Also tried to Clean the script, rebuild the script then save it. Nope, it would not recognize the new assembly

    But if I started with a new Script task. Then added the new refrence you advised, which it found, then copy pasted all of my code from old Script task into this new scrip task, the code worked and debug worked fine.  Is there some type of cleaning of the references I could do that would have to same me doing this craziness of recreating a script task and copying it into it?

    Thanks

    MG


    • Edited by mg101 Monday, April 29, 2019 5:41 PM wording
    Monday, April 29, 2019 5:39 PM
  • Hi MG,

    Well, I can reproduce the problem exactly, and find some interesting things in the package file. 

    Before you edit the package file, please make a copy of the original package. 

    You need to remove the COMReference node(marked in following picture) from the package file(.dtsx), using text editor, like Notepad++. Then the breakpoints will work fine, with the new reference(Microsoft.Office.Interop.Excel.dll). 

    It seems that Script Task breakpoints can't work with COM reference, not only "Microsoft Excel 16.0 Object Library". But, even if the COM reference is deleted and replaced by the new reference, the package content is not updated. 


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by mg101 Tuesday, April 30, 2019 4:44 PM
    Tuesday, April 30, 2019 3:27 AM
  • HI Yang,

    Thanks for all of this!!! Debug works fine with this work around.

    But this is one more caveat.

    So, lets step back a moment. When you had Excel 16 (not your new assembly) but the regular refererence, VS adds Excel 16, Office 16, and VB For Application Extensibiltiy 5.3. All of those will show up in COMREF.

    As a test, I just deleted the Excel 16 and office 16 references in the COMREF.  VB App Ext remained.

    Nope - that killed debug too. As you mentioned - it there is a com object in that thing, debug is disabled.

    If I added a reference in the scrip task for office 16 object library, VB for Application Exensitility 5.3 got added as well to COMREF. And if I added a reference in the scrip task for VB for Application Extensitility 5.3, Office 16 object library was added as well. Of course both prevent debugger from launching.

    thx again!!!  Others have this problem and hopefully they will read this as well.

    MG

    Tuesday, April 30, 2019 4:44 PM
  • Hi Yang.

    One more question.

    What is the difference between selecting the COM object in the Refference window for Excle 2016 and using the DLL you gave me in C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0__71e9bce111e9429c ?

    is the one you gave me not actually a COM object?

    Thanks,

    MG

    Sunday, May 5, 2019 10:06 PM