Adding a namespace that doesn't exist for a SSIS Script Transformation

I need to add the Microsoft.Office.Interop.Excel namespace reference so I can apply an Excel macro in a SSIS Script Transformation Component.  When I edit the Script Transformation and go to add a Reference, Microsoft.Office.Interop.Excel does not exist.  I've made sure that .NET Programmability Support was selected when I installed Microsoft Office and I've also installed the Visual Studio 2010 Tools for Office Runtime.

The code I need to add looks like this but it don't build without the namespace reference.  Thanks!!
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkBook = xlApp.Workbooks.Open("C:\\ExcelDirectory\\DATA.xlsm"); // absolute path needed
xlApp.Run("Formatting"); // method overloads allow you to send it parameters, etc.
xlWorkBook.Close(true); // first parameter is SaveChanges
xlApp.Quit();

Open in new window

BasssqueAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Megan BrooksConnect With a Mentor SQL Server ConsultantCommented:
It's actually the assembly reference that you need to add, before your code will be able to use the namespace. I haven't had to do this myself, but here's an article from 2011 that describes steps (that I have not tested).

SSIS Script Task and Microsoft Office Automation
1
 
BasssqueAuthor Commented:
That was helpful.  I installed the Primary Interop Assemblies Redistributable and it showed up under COM/Type Libraries opposed to Assemblies
0
 
Megan BrooksSQL Server ConsultantCommented:
The labeling may depend on the version of SSIS/VSTS. Adding assembly references generally is straightforward for assemblies installed in the GAC, which is evidently where the redistributable installs this set.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.