SSIS: VBScript for Excel inserting 0 and get columns

/*
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Diagnostics;
namespace ST_f1f4f90e7d8e4a25b9330e1d946d8c95.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 this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.
To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
To use the connections collection use something like the 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 from this method, set the value of Dts.TaskResult to indicate success or failure.
To open Help, press F1.
*/
public void Main()
{
int error_flag = 0;
Dts.Variables["ErrorFileName"].Value = Dts.Variables["FileName"].Value.ToString().Replace(".xls", "_errors.xls");
//SET NUMERICS IN EXCEL FILE TO ZERO TO AVOID CRAPPY JET DRIVER INCORRECTLY SETTING COLUMN TYPES AT RUNTIME
string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=" + Dts.Variables["FileName"].Value.ToString() + @";
Extended Properties=
""Excel 8.0;HDR=YES;""";
System.Data.OleDb.OleDbConnection ExcelConnection =
new System.Data.OleDb.OleDbConnection
(ConnectionString);
string ExcelQuery;
List numericExcelColumns = new List{
//"[AnnualSalary]",
"[Salaries]",
"[Overtime]",
"[Pay In Lieu Of Holiday]",
"[Severances]",
"[Employers Social Taxes]",
"[SPOT Bonus]",
"[STIP Bonus]",
"[Contractual STIP]",
"[Earnout STIP]",
"[Sales Commision Bonus]",
"[Taxable Value Of PSA Payout]",
"[Taxable Value Of Other Restricted Payout]",
"[Taxable Value Of LEAP Payout]",
"[Taxable Value Of Worldwide Ownership Plan On Exercise Payout]",
"[Taxable Value Of Executive Option Plan On Exercise Payout]",
"[Pensions - Defined Contribution Schemes]",
"[Pensions - Defined Benefit Schemes]",
"[Medical Insurance]",
"[Life Assurance]",
"[PHI/LTD]",
"[Critical Illness]",
"[Car - Lease Or Depreciations]",
"[Car Allowance]",
"[Other Car]",
"[Housing]",
"[School Fees]",
"[COLA]",
"[Home Leave]",
"[Club Membership]",
"[Relocation Expenses]"};
try
{
System.Data.OleDb.OleDbCommand ExcelCommand;
//UPDATE
ExcelConnection.Open();
ExcelCommand = new System.Data.OleDb.OleDbCommand();
ExcelCommand.Connection = ExcelConnection;
int nocols = numericExcelColumns.Count;
//Fill DataTable with columns information
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", ExcelConnection);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet, "ExcelInfo");
//DataGrid1.DataSource = myDataSet.Tables["ExcelInfo"].DefaultView;
//DataGrid1.DataBind();
DataTable myDataTable = ExcelConnection.GetSchema("Columns");//, new string[] { "Test", "Tables" });
string columnName;
foreach (string excelColumn in numericExcelColumns)
{
//For every Column in the table
for (int i = 0; i < myDataSet.Tables[0].Columns.Count; i++)
{
//Get column name and type
columnName = myDataSet.Tables[0].Columns[i].ToString();
if ("[" + columnName + "]" == excelColumn)
{
error_flag = error_flag + 1;
break;
}
}
ExcelQuery = string.Format("update [Sheet1$] set {0} = '0' where {0} is null and EmployeeID is not null", excelColumn);
ExcelCommand.CommandText = ExcelQuery;
ExcelCommand.ExecuteNonQuery();
}
//ExcelQuery = "update [Sheet1$] set [SPOT Bonus] = '1' where len([SPOT Bonus]) = 0";//works
//ExcelQuery = "update [Sheet1$] set [SPOT Bonus] = '9' where [SPOT Bonus] = 1";//works
//ExcelQuery = "update [Sheet1$] set [SPOT Bonus] = '9' where [SPOT Bonus] is null";//works
//ExcelQuery = "update [Sheet1$] set [Overtime] = '0' where [Overtime] is null";//works
ExcelCommand = null;
ExcelConnection.Close();
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
}
if (error_flag == 30)
{
Dts.TaskResult = (int)ScriptResults.Success;
}
else
{
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
}
}