using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Data.OleDb;
public void Main()
{
OleDbConnection objConnection = null;
string strConnection= string.Empty;
try
{
string[] sourcefiles = Directory.GetFiles(Dts.Variables["User::FilePath"].Value.ToString());
string strFileQualifiedName = Convert.ToString(Dts.Variables["User::FilePath"].Value)
+ "\\" + Convert.ToString(Dts.Variables["User::FileName"].Value);
//Provider for excel 2007
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + strFileQualifiedName + ";Extended Properties=\"Excel 12.0 XML;HDR=NO\"";
if (Directory.Exists(Convert.ToString(Dts.Variables["User::FilePath"].Value)))
{
FileInfo fileInfo = new FileInfo(strFileQualifiedName);
if (fileInfo.Exists)
{
// I think it fails in the next line
objConnection = new OleDbConnection(strConnection);
objConnection.Open();
DataRow[] drWorkSheets = objConnection.GetSchema("Tables").Select("TABLE_NAME = '" + Convert.ToString(Dts.Variables["User::WorkSheetName"].Value).Trim() + "$'");
if (drWorkSheets.Length == 1)
{
Dts.Variables["User::IsWorkSheetFound"].Value = true;
}
else
{
Dts.Variables["User::IsWorkSheetFound"].Value = false;
}
objConnection.Close();
}
else
{
Dts.Variables["User::IsWorkSheetFound"].Value = false;
}
}
else
{
Dts.Variables["User::IsWorkSheetFound"].Value = false;
}
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
Dts.TaskResult = (int)ScriptResults.Failure;
Dts.Variables["User::StatusDescription"].Value = "Check Work Sheet Error: " + ex.Message + "Stack Trace: " + ex.StackTrace + "Connection string: " + strConnection;
}
finally
{
if (objConnection != null)
{
if (objConnection.State == ConnectionState.Open)
{
objConnection.Close();
}
objConnection.Dispose();
objConnection = null;
}
}
}
}
}
Stake Trace :
Stack Trace:at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at ST_a451219206e54e21b4be51b95b706a1b.csproj.ScriptMain.Main()Connection string: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\RRWIN-FSSSISE01\File\SSISData\LoadFiles\Import\TrialBalanceDef_08afd8dc-38b7-4ea5-80c3-8b7e7ce383b5\0552d0b9-8abe-4b3f-9b7b-43e47c1d2108TrialBalanceDefinitionImport.xlsx;Extended Properties="Excel 12.0 XML;HDR=NO"
How i resolve it most of the time
I restart the app Server by going to the Task Manager 
As it only happen in The Stage and Production environment. If i take the backup of DB and work in DEV Or QA there is not issue. Restarting the Service only works; Eventhough, the Memory consumption of the Service is not Overloaded it is only consuming about 30-35 percentage of memory. Please let me know any idea you might have i will greatly appreciate it . Thank you.
Together with Error Message
SISServiceBiz.RunPackage called: package name = WorkbookDefinitionsImport.dtsx ERROR: Data Processor Failed. Error Message: Check Work Sheet Error: System resource exceeded.Stack Trace: at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
