2

I know there are many questions that say Object reference not set to an instance of an object but my question is different but gives the same error

I am running this script in a SSIS package inside a Script task which builds and complies fine when ran through visual studio 2013 professional both local or on the server (where package is deployed).

But when I run the sql job the package fails and in my log file it writes the following error Formatting file method started.. Error Occurred ..Please see the error Message :Object reference not set to an instance of an object.

I am unable to understand why on server it builds successfully when ran in visual studio but fails in the 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 as this is not present is vb code from which code is 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;
            }
            finally
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcelWbk);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcelApp);
                System.GC.Collect();
            }
        }
  • I would point out that the use of Office interop in this scenario is [not recommended or supported](https://support.microsoft.com/en-gb/kb/257757) by Microsoft. – Charles Mager Jun 01 '16 at 19:32
  • @CharlesMager the office version I am using is 2010, some other packages also use Microsoft.Office.Interop.Excel and they run fine –  Jun 01 '16 at 19:34
  • good for you, but it's still not recommended for all the reasons listed in the KB article. There are plenty of managed alternatives (that also have the benefit of being a heck of a lot faster). If you want to solve this issue, you need to debug it to work out what is null. – Charles Mager Jun 01 '16 at 19:36
  • @CharlesMager I did I check the variables and it built successfully.Is there a way I can debug when the sql job is ran? –  Jun 01 '16 at 19:38

0 Answers0