Create Excel Files Microsoft.Office.Interop.Excel in SSIS C# Scripting.

hi guys,

i have issues specific box when run c# script supposed generate excel file within ssis package using interops.

the script running..

  /*    microsoft sql server integration services script task    write scripts using microsoft visual c# 2008.    scriptmain entry point class of script.  */        //using   using system;  using system.collections.generic;   using system.data;  using microsoft.sqlserver.dts.runtime;  using system.windows.forms;  using excel = microsoft.office.interop.excel;   using microsoft.office.core;    namespace st_8aa38d8ae4e54a7583cab65abaa02f3b.csproj  {    [system.addin.addin("scriptmain", version = "1.0", publisher = "", description = "")]    public partial class scriptmain : microsoft.sqlserver.dts.tasks.scripttask.vstartscriptobjectmodelbase    {        #region vsta generated code      enum scriptresults      {        success = microsoft.sqlserver.dts.runtime.dtsexecresult.success,        failure = microsoft.sqlserver.dts.runtime.dtsexecresult.failure      };      #endregion        /*  		the execution engine calls method when task executes.  		to access object model, use dts property. connections, variables, events,  		and logging features available members of dts property shown in following examples.    		to reference variable, call dts.variables["mycasesensitivevariablename"].value;  		to post log entry, call dts.log("this log text", 999, null);  		to fire event, call dts.events.fireinformation(99, "test", "hit message", "", 0, true);    		to use connections collection use following:  		connectionmanager cm = dts.connections.add("oledb");  		cm.connectionstring = "data source=localhost;initial catalog=adventureworks;provider=sqlncli10;integrated security=sspi;auto translate=false;";    		before returning method, set value of dts.taskresult indicate success or failure.  		  		to open help, press f1.  	*/        public void main()      {          excel.application myexcelapp;        excel.workbooks myexcelworkbooks;        excel.workbook myexcelworkbook;        object misvalue = system.reflection.missing.value;        myexcelapp = new excel.application();        //myexcelapp.visible = true;        myexcelworkbooks = myexcelapp.workbooks;        string filename = "d:\\iccvisits\\iccdailytotals2011.xls";        //myexcelworkbook = myexcelworkbooks.open(filename,true);        myexcelworkbook = myexcelworkbooks.open(filename, misvalue, misvalue, misvalue, misvalue, misvalue, misvalue, misvalue, misvalue, misvalue, misvalue, misvalue, misvalue, misvalue, misvalue);        //myexcelworkbook = myexcelworkbooks.add();        excel.worksheet myworksheet = (excel.worksheet)myexcelworkbook.worksheets["sheet1"];                          myworksheet.cells[1, 1] = "testdata";                //myexcelworkbook.worksheets.add["barcelona"];        //myexcelworkbook.sheets.add["barca"];        //excel.worksheet newworksheet;        //newworksheet = (excel.worksheet)myexcelworkbook.worksheets.add(misvalue, misvalue, misvalue, misvalue);        //newworksheet.name = "barcelona";        //string filename = "c:\\temp\\test.xls";        myexcelworkbook.save();        //myexcelworkbook.saveas (filename);        //myexcelworkbook.close();        myexcelapp.quit();        // todo: add code here             // todo: add code here        dts.taskresult = (int)scriptresults.success;      }    }  }  

the error message getting:

 

error: system.reflection.targetinvocationexception: exception has been thrown target of invocation. ---> system.runtime.interopservices.comexception (0x8002000b): invalid index. (exception hresult: 0x8002000b (disp_e_badindex))

   at microsoft.office.interop.excel.sheets.get__default(object index)

   at st_8aa38d8ae4e54a7583cab65abaa02f3b.csproj.scriptmain.main()

   --- end of inner exception stack trace ---

   at system.runtimemethodhandle._invokemethodfast(object target, object[] arguments, signaturestruct& sig, methodattributes methodattributes, runtimetypehandle typeowner)

   at system.reflection.runtimemethodinfo.invoke(object obj, bindingflags invokeattr, binder binder, object[] parameters, cultureinfo culture, boolean skipvisibilitychecks)

   at system.reflection.runtimemethodinfo.invoke(object obj, bindingflags invokeattr, binder binder, object[] parameters, cultureinfo culture)

   at system.runtimetype.invokemember(string name, bindingflags bindingflags, binder binder, object target, object[] providedargs, parametermodifier[] modifiers, cultureinfo culture, string[] namedparams)

   at microsoft.sqlserver.dts.tasks.scripttask.vstataskscriptingengine.executescript()

besides interops other options have generate excel files way heavily customized. ssrs export @ least in 2008 not option us, other tools might use if interops cranky, seems?..and dba don't have excel installed on productionserver...:)

anyway, input appreciated.

ludwig

 

 

ok, boiled down to:

1) incomplete ms office installation, and/or

2) importer use of ms interop object reference.

to recover please make sure excel runnable. if not install using proper media, in code add reference microsoft excel 11 object library

as guide please use msdn article http://msdn.microsoft.com/en-us/library/ms173186%28v=vs.80%29.aspx

ps: have non ssis issue. create basic excel sheet (so far looks need basics) can use excel destination ( basic example: http://www.bidn.com/blogs/danmatisis/ssas/843/ssis-using-an-excel-data-destination)

---

arthur my blog

SQL Server  >  SQL Server Integration Services