SSIS Script Reference Missing

• Mark Finnie

  SSC Eights!

  Points: 988

  More actions

  September 8, 2016 at 12:57 am

  #400692

  I cannot permanently add the assembly Microsoft.Office.Interop.Excel into a SSIS script. While in the script editor it works just fine, but if I exit the script editor then go back into it the reference is gone.

  I have searched online and found others who have similar problems, e.g. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9a6b1867-b787-4f4d-9a57-abf651b2292c/referencing-microsoftofficeinteropexceldll?forum=sqlintegrationservices

  However I cannot get any suggested solution to work.

  I can see the assembly is included in the C:\Windows\assembly folder. I am using SQL Server 2008 R2 on a 64 bit computer. The BIDS version is 9.0.30729.4462. The operating system is Windows 7 Professional sp1.

  If I add the reference with the script editor open, then in the Project Explorer window under References I have the following 3 Microsoft references:

  -Microsoft.Office.Interop.Excel

  -Microsoft.SqlServer.ManagedDTS

  -Microsoft.SqlServer.ScriptTask

  When I close the editor and reopen it the first reference is gone but the other two remain.

  Can someone please tell me how I can add the reference permanently?

• Mark Finnie

  SSC Eights!

  Points: 988

  More actions

  September 13, 2016 at 8:50 pm

  #1900710

  If no-one can tell me how to save the reference permanently I will have to change the Excel file to a macro enabled workbook and run code when user open the file. I was hoping to avoid that if possible. Can anyone tell me how to resolve this problem?

  Thanks in advance.

• benscooper01

  Valued Member

  Points: 70

  More actions

  September 14, 2016 at 1:30 am

  #1900730

  Hi Mark,

  Have you tried building the project (in Project Explorer window) before exiting the script editor? I find my freshly added references disappear if I don't remember to do this.

  Cheers

  Ben

• Mark Finnie

  SSC Eights!

  Points: 988

  More actions

  September 15, 2016 at 9:59 pm

  #1901260

  Thanks Ben. That worked. I thought when you save the script and exit it would keep the references, but apparently not. When I did the Build it saved the reference. Thank you.