I have a SSIS Package that run's fine locally but it is failing while I run it on a job with the following error
Error Occured ..Please see the error Message :Object reference not set to an instance of an object.
The code is below
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;
object missing = Missing.Value;
public int Main()
{
//--------------------------- Declarations -----------------------//
StringBuilder sb = new StringBuilder();
string StrDBConnection = string.Empty;
string LogFilePath = string.Empty;
string strExcelDataOutPut = string.Empty;
string sTempFile1 = string.Empty;
string sTempFile2 = string.Empty;
string sFilename = string.Empty;
//--------------- Input Varaiables ---------------//
LogFilePath = Dts.Variables["User::SSIS_DM_Log"].Value.ToString() + Dts.Variables["User::CreateMonthlyJobPostingSummary_slogFile"].Value.ToString();
strExcelDataOutPut = Dts.Variables["User::SSIS_DM_Output"].Value.ToString();
LogFilePath = LogFilePath + DateTime.Today.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + ".log";
sFilename = Dts.Variables["User::CreateMonthlyJobPostingSummary_sFileName"].Value.ToString();
//----------------------------------------------------------------------//
Dts.TaskResult = (int)ScriptResults.Success;
//------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------;
//---Create the FileSystem Object;
try
{
sb.AppendLine("Formatting of Job posting summary file started..");
FormatFile(strExcelDataOutPut + sFilename);
sb.AppendLine("Formatting file method ended.");
}
catch (Exception ex)
{
using (System.IO.StreamWriter outfile = new System.IO.StreamWriter(LogFilePath))
{
sb.AppendLine("Error Occured ..Please see the error Message :" + ex.Message);
outfile.Write(sb.ToString());
}
Dts.TaskResult = (int)ScriptResults.Failure;
}
return Dts.TaskResult;
}
#region format File
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 objWrksheetSummary = default(Excel.Worksheet);
Microsoft.Office.Interop.Excel.Worksheet objWrksheetDetail = default(Excel.Worksheet);
try
{
if (System.IO.File.Exists(sFilename))
{
int RowCount, RowCountS2, RowCountDetail;
int cStart = 6;
string RunYr = string.Empty, RunQtr = string.Empty;
string Value = string.Empty, PrintRow = string.Empty;
string Crangevalue = string.Empty, Crange2Value = string.Empty;
Excel.Range Crange, Crange1, Crange2;
string[] Arry;
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);
objWrksheetSummary = (Excel.Worksheet)objExcelWbk.Worksheets["Summary"];
objWrksheetDetail = (Excel.Worksheet)objExcelWbk.Worksheets["Detail"];
((Microsoft.Office.Interop.Excel._Worksheet)objWrksheetSummary).Activate();
Crange = (Excel.Range)objWrksheetDetail.get_Range((object)"A2:A2", missing);
RunYr = (Crange.Value2 == null ? "" : Crange.Value2.ToString());
//RowCountS2 = objWrksheetSummary.UsedRange.Rows.Count;
//Issue Fix For Defect #335
Crange = (Excel.Range)objWrksheetSummary.Cells[1, 30];
Crange.Formula = "=COUNTA(a2:a20000)";
RowCountS2 = Convert.ToInt32(Crange.Value2.ToString());
Crange = (Excel.Range)objWrksheetSummary.Cells[1, 2];
Crange.Value2 = RunYr;
RunFormat(ref objWrksheetSummary, 'B', RowCountS2 + 1, "Number");
RunFormat(ref objWrksheetSummary, 'C', RowCountS2 + 1, "Number");
RunFormat(ref objWrksheetSummary, 'D', RowCountS2 + 1, "Number");
RunFormat(ref objWrksheetSummary, 'E', RowCountS2 + 1, "Number");
RunFormat(ref objWrksheetSummary, 'F', RowCountS2 + 1, "Number");
RunFormat(ref objWrksheetSummary, 'G', RowCountS2 + 1, "Number");
RunFormat(ref objWrksheetSummary, 'H', RowCountS2 + 1, "Number");
RunFormat(ref objWrksheetSummary, 'I', RowCountS2 + 1, "Number");
RunFormat(ref objWrksheetSummary, 'J', RowCountS2 + 1, "Number");
RunFormat(ref objWrksheetSummary, 'K', RowCountS2 + 1, "Number");
RunFormat(ref objWrksheetSummary, 'L', RowCountS2 + 1, "Number");
RunFormat(ref objWrksheetSummary, 'M', RowCountS2 + 1, "Number");
RunFormat(ref objWrksheetSummary, 'N', RowCountS2 + 1, "Number");
Crange = (Excel.Range)objWrksheetSummary.get_Range((object)"A" + (RowCountS2 + 2).ToString() + (object)":A" + (RowCountS2 + 2).ToString(), missing);
Crange.Value2 = "Grand Total";
Crange = (Excel.Range)objWrksheetSummary.get_Range((object)"B" + (RowCountS2 + 2).ToString() + (object)":B" + (RowCountS2 + 2).ToString(), missing);
Crange.Formula = "=sum(B2:B" + (RowCountS2 + 1).ToString() + ")";
Crange.Copy(missing);
Crange1 = (Excel.Range)objWrksheetSummary.get_Range((object)"C" + (RowCountS2 + 2).ToString() + (object)":N" + (RowCountS2 + 2).ToString(), missing);
Crange1.PasteSpecial(Excel.XlPasteType.xlPasteFormulas, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, missing, missing);
Crange1 = (Excel.Range)objWrksheetSummary.get_Range((object)"A" + (RowCountS2 + 2).ToString() +(object) ":N" + (RowCountS2 + 2).ToString(), missing);
Crange1.Font.FontStyle = "Bold";
PrintRow = "A1:N" + (RowCountS2 + 2).ToString();
objWrksheetSummary.PageSetup.PrintArea = PrintRow;
// Start - Issue fix: Focus cursor to top of the sheet
Crange1 = (Excel.Range)objWrksheetSummary.Cells.get_Range((object)"A1", (object)"A1");
Crange1.Select();
// End - Issue fix: Focus cursor to top of the sheet
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();
}
}
#endregion
#region Run Format
public void RunFormat(ref Excel.Worksheet objWrksheet, char sColumn, int row, string Formattype)
{
int iNextCol = System.Convert.ToInt32(sColumn) + 1 - 64;
Excel.Range CRange, Crange1, Crange2;
CRange = (Excel.Range)objWrksheet.Columns[iNextCol, Missing.Value];
CRange.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, Missing.Value);
CRange = (Excel.Range)objWrksheet.Cells[2, iNextCol];
switch (Formattype)
{
case "Date":
CRange.FormulaR1C1 = "=IF(RC[-1]<>\"\"\"\",DATEVALUE(RC[-1]),\"\"\"\")";
break;
case "Number":
CRange.FormulaR1C1 = "=IF(RC[-1]<>\"\"\"\",RC[-1]*1,\"\"\"\")";
break;
}
if (row > 2)
{
Crange2 = (Excel.Range)objWrksheet.get_Range((Excel.Range)objWrksheet.Cells[2, iNextCol], (Excel.Range)objWrksheet.Cells[row, iNextCol]);
CRange.AutoFill(Crange2, Excel.XlAutoFillType.xlFillDefault);
}
CRange = (Excel.Range)objWrksheet.get_Range((Excel.Range)objWrksheet.Cells[2, iNextCol], (Excel.Range)objWrksheet.Cells[row, iNextCol]);
CRange.Copy(Missing.Value);
Crange1 = (Excel.Range)objWrksheet.get_Range((object)sColumn + "2:" + (object)sColumn + (object)row.ToString(), missing);
Crange1.PasteSpecial(Excel.XlPasteType.xlPasteValuesAndNumberFormats, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
Crange1 = (Excel.Range)objWrksheet.Columns[iNextCol, Missing.Value];
Crange1.Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft);
Crange1 = (Excel.Range)objWrksheet.get_Range((object)sColumn + "2:" + (object)sColumn + (object)row.ToString(), missing);
switch (Formattype)
{
case "Date":
Crange1.NumberFormat = "d-mmm-yyyy";
Crange1.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case "Number":
Crange1.NumberFormat = "0";
Crange1.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
}
}
#endregion
}
}
