sql server - C# Excel Object reference not set to an instance of an object -
i know there many questions object reference not set instance of object question different gives same error
i running script in ssis package inside script task builds , complies fine when ran through visual studio 2013 professional both local or on server (where package deployed).
but when run sql job package fails , in log file writes following error formatting file method started.. error occurred ..please see error message :object reference not set instance of object.
i unable understand why on server builds when ran in visual studio fails in sql job?
using system; using system.data; using microsoft.sqlserver.dts.runtime; using system.windows.forms; using system.io; using system.text; using system.data.sqlclient; using system.diagnostics; using system.reflection; using excel = microsoft.office.interop.excel; using microsoft.csharp; public void formatfile(string sfilename) { microsoft.office.interop.excel.application objexcelapp = new excel.application(); microsoft.office.interop.excel.workbook objexcelwbk = default(excel.workbook); microsoft.office.interop.excel.worksheet objwrksheetgp = default(excel.worksheet); microsoft.office.interop.excel.worksheet objwrksheetsummary = default(excel.worksheet); microsoft.office.interop.excel.worksheet objwrksheetdetail = default(excel.worksheet); try { object missing = missing.value; int rowcount, rowcountgp, rowcountdetail; int cstart = 2, rowstart = 0, rowend = 2; objexcelapp.displayalerts = false; objexcelapp.visible = false; objexcelwbk = objexcelapp.workbooks.open(sfilename, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, true); objwrksheetgp = (excel.worksheet)objexcelwbk.worksheets["template"]; objwrksheetdetail = (excel.worksheet)objexcelwbk.worksheets["detail"]; objwrksheetsummary = (excel.worksheet)objexcelwbk.worksheets["summary"]; ((microsoft.office.interop.excel._worksheet)objwrksheetdetail).activate(); rowcountgp = objwrksheetgp.usedrange.rows.count; rowcount = objwrksheetdetail.usedrange.rows.count; excel.range crange, crange1; crange = (excel.range)objwrksheetdetail.get_range("b2:ao" + rowcount.tostring(), missing); crange.copy(missing); crange1 = (excel.range)objwrksheetsummary.cells[3, 3]; crange1.pastespecial(excel.xlpastetype.xlpastevalues, excel.xlpastespecialoperation.xlpastespecialoperationnone, missing, missing); runformat(ref objwrksheetgp, 'b', rowcountgp, "number"); while (cstart <= rowcountgp) { crange1 = (excel.range)objwrksheetgp.get_range("b" + cstart.tostring() + ":b" + cstart.tostring(), missing); rowcountdetail = convert.toint32(crange1.value2); rowstart = rowend + 1; rowend = rowstart + rowcountdetail - 1; crange1 = (excel.range)objwrksheetsummary.get_range("a" + rowend.tostring() + ":ap" + rowend.tostring(), missing); crange1.interior.colorindex = 15; crange1.borders[excel.xlbordersindex.xledgebottom].linestyle = excel.xllinestyle.xlcontinuous; crange1.borders[excel.xlbordersindex.xledgebottom].weight = excel.xlborderweight.xlmedium; crange1.borders[excel.xlbordersindex.xledgebottom].colorindex = excel.xlcolorindex.xlcolorindexautomatic; cstart = cstart + 1; } //commented line of code not present vb code code regenerated. /*crange = objwrksheetdetail.get_range("e2:e" + rowcount.tostring(), missing); crange1 = objwrksheetsummary.get_range("e3:e" + (rowcount + 1).tostring(), missing); crange1.value2 = crange.value2;*/ crange1 = objwrksheetsummary.get_range("d2:e" + rowcount.tostring(), missing); crange1.font.bold = true; crange1 = objwrksheetsummary.get_range("d2:d" + (rowcount + 1).tostring(), missing); crange1.borders[excel.xlbordersindex.xledgeright].linestyle = excel.xllinestyle.xlcontinuous; crange1.borders[excel.xlbordersindex.xledgeright].weight = excel.xlborderweight.xlmedium; crange1.borders[excel.xlbordersindex.xledgeright].colorindex = excel.xlcolorindex.xlcolorindexautomatic; crange1 = objwrksheetsummary.get_range("e2:e" + (rowcount + 1).tostring(), missing); crange1.borders[excel.xlbordersindex.xledgeright].linestyle = excel.xllinestyle.xlcontinuous; crange1.borders[excel.xlbordersindex.xledgeright].weight = excel.xlborderweight.xlmedium; crange1.borders[excel.xlbordersindex.xledgeright].colorindex = excel.xlcolorindex.xlcolorindexautomatic; crange1 = objwrksheetsummary.get_range("i2:i" + (rowcount + 1).tostring(), missing); crange1.borders[excel.xlbordersindex.xledgeright].linestyle = excel.xllinestyle.xlcontinuous; crange1.borders[excel.xlbordersindex.xledgeright].weight = excel.xlborderweight.xlmedium; crange1.borders[excel.xlbordersindex.xledgeright].colorindex = excel.xlcolorindex.xlcolorindexautomatic; crange1 = objwrksheetsummary.get_range("l2:l" + (rowcount + 1).tostring(), missing); crange1.borders[excel.xlbordersindex.xledgeright].linestyle = excel.xllinestyle.xlcontinuous; crange1.borders[excel.xlbordersindex.xledgeright].weight = excel.xlborderweight.xlmedium; crange1.borders[excel.xlbordersindex.xledgeright].colorindex = excel.xlcolorindex.xlcolorindexautomatic; crange1 = objwrksheetsummary.get_range("o2:o" + (rowcount + 1).tostring(), missing); crange1.borders[excel.xlbordersindex.xledgeright].linestyle = excel.xllinestyle.xlcontinuous; crange1.borders[excel.xlbordersindex.xledgeright].weight = excel.xlborderweight.xlmedium; crange1.borders[excel.xlbordersindex.xledgeright].colorindex = excel.xlcolorindex.xlcolorindexautomatic; crange1 = objwrksheetsummary.get_range("r2:r" + (rowcount + 1).tostring(), missing); crange1.borders[excel.xlbordersindex.xledgeright].linestyle = excel.xllinestyle.xlcontinuous; crange1.borders[excel.xlbordersindex.xledgeright].weight = excel.xlborderweight.xlmedium; crange1.borders[excel.xlbordersindex.xledgeright].colorindex = excel.xlcolorindex.xlcolorindexautomatic; crange1 = objwrksheetsummary.get_range("u2:u" + (rowcount + 1).tostring(), missing); crange1.borders[excel.xlbordersindex.xledgeright].linestyle = excel.xllinestyle.xlcontinuous; crange1.borders[excel.xlbordersindex.xledgeright].weight = excel.xlborderweight.xlmedium; crange1.borders[excel.xlbordersindex.xledgeright].colorindex = excel.xlcolorindex.xlcolorindexautomatic; crange1 = objwrksheetsummary.get_range("x2:x" + (rowcount + 1).tostring(), missing); crange1.borders[excel.xlbordersindex.xledgeright].linestyle = excel.xllinestyle.xlcontinuous; crange1.borders[excel.xlbordersindex.xledgeright].weight = excel.xlborderweight.xlmedium; crange1.borders[excel.xlbordersindex.xledgeright].colorindex = excel.xlcolorindex.xlcolorindexautomatic; crange1 = objwrksheetsummary.get_range("aa2:aa" + (rowcount + 1).tostring(), missing); crange1.borders[excel.xlbordersindex.xledgeright].linestyle = excel.xllinestyle.xlcontinuous; crange1.borders[excel.xlbordersindex.xledgeright].weight = excel.xlborderweight.xlmedium; crange1.borders[excel.xlbordersindex.xledgeright].colorindex = excel.xlcolorindex.xlcolorindexautomatic; crange1 = objwrksheetsummary.get_range("ad2:ad" + (rowcount + 1).tostring(), missing); crange1.borders[excel.xlbordersindex.xledgeright].linestyle = excel.xllinestyle.xlcontinuous; crange1.borders[excel.xlbordersindex.xledgeright].weight = excel.xlborderweight.xlmedium; crange1.borders[excel.xlbordersindex.xledgeright].colorindex = excel.xlcolorindex.xlcolorindexautomatic; crange1 = objwrksheetsummary.get_range("ag2:ag" + (rowcount + 1).tostring(), missing); crange1.borders[excel.xlbordersindex.xledgeright].linestyle = excel.xllinestyle.xlcontinuous; crange1.borders[excel.xlbordersindex.xledgeright].weight = excel.xlborderweight.xlmedium; crange1.borders[excel.xlbordersindex.xledgeright].colorindex = excel.xlcolorindex.xlcolorindexautomatic; crange1 = objwrksheetsummary.get_range("aj2:aj" + (rowcount + 1).tostring(), missing); crange1.borders[excel.xlbordersindex.xledgeright].linestyle = excel.xllinestyle.xlcontinuous; crange1.borders[excel.xlbordersindex.xledgeright].weight = excel.xlborderweight.xlmedium; crange1.borders[excel.xlbordersindex.xledgeright].colorindex = excel.xlcolorindex.xlcolorindexautomatic; crange1 = objwrksheetsummary.get_range("am2:am" + (rowcount + 1).tostring(), missing); crange1.borders[excel.xlbordersindex.xledgeright].linestyle = excel.xllinestyle.xlcontinuous; crange1.borders[excel.xlbordersindex.xledgeright].weight = excel.xlborderweight.xlmedium; crange1.borders[excel.xlbordersindex.xledgeright].colorindex = excel.xlcolorindex.xlcolorindexautomatic; crange1 = objwrksheetsummary.get_range("ap2:ap" + (rowcount + 1).tostring(), missing); crange1.borders[excel.xlbordersindex.xledgeright].linestyle = excel.xllinestyle.xlcontinuous; crange1.borders[excel.xlbordersindex.xledgeright].weight = excel.xlborderweight.xlmedium; crange1.borders[excel.xlbordersindex.xledgeright].colorindex = excel.xlcolorindex.xlcolorindexautomatic; objwrksheetdetail.visible = excel.xlsheetvisibility.xlsheethidden; objwrksheetgp.visible = excel.xlsheetvisibility.xlsheethidden; objexcelwbk.save(); objexcelwbk.close(true, missing, missing); objexcelapp.quit(); } catch { system.runtime.interopservices.marshal.releasecomobject(objexcelwbk); system.runtime.interopservices.marshal.releasecomobject(objexcelapp); throw; } { system.runtime.interopservices.marshal.releasecomobject(objexcelwbk); system.runtime.interopservices.marshal.releasecomobject(objexcelapp); system.gc.collect(); } }
