This is my code to read columns from excel sheet and save into the data table which is working fine in my Visual Studio 2010 Console Application but when i am using this code in my SSIS project using Visual Studio 2008 and i have used the exact code in SSIS package then i am getting an error The Microsoft.ACE.OLEDB.12.0 provider is not registered on the local Machine
I am new to SSIS and my other SSIS VS2008 projects are working fine but in this one i had to read the data from excel so i am using my existing code but getting this error .
//excel to data table
public System.Data.DataTable exceltodatatable()
{
System.Data.DataTable myTable = null; ;
try
{
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.DataSet DtSet;
System.Data.OleDb.OleDbDataAdapter MyCommand;
// string path = @"D:\projects\excel\spec.xlsx";
//string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";
MyConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='D:\\projects\\excel_tEST\\ConsoleApplication13\\ConsoleApplication13\\bin\\Debug\\excel\\clublist.xlsx';Extended Properties=Excel 12.0;");
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
MyCommand.TableMappings.Add("Table", "TestTable");
DtSet = new System.Data.DataSet();
MyCommand.Fill(DtSet);
myTable = DtSet.Tables[0];
MyConnection.Close();
myTable.Columns.Add("someNewColumn", typeof(string));
int i = 0;
foreach (DataRow drOutput in myTable.Rows)
{
// drOutput["Preferred Version"] = drOutput["Preferred Version"].ToString().Replace("**", "yes");
//your remaining codes
int count = drOutput[0].ToString().Length;
if (count <= 24)
{
myTable.Rows[i][1] = "3"; ;
}
else
{
myTable.Rows[i][1] = "4"; ;
}
i++;
}
}
catch (Exception ex)
{
//MessageBox.Show(ex.ToString());
}
// myTable.Columns.Remove("Author");
return myTable;
}
This is the Screenshot of my error . Any help?
This is the error given when the OLEDB driver is not able to find the provider you specified. Given the fact that it works in Visual Studio (I'm assuming on the same computer), you may look into whether you're building the application as 64-bit rather than 32-bit. The provider you have installed is likely x86, and therefore has to be called from an x86 application.
Try setting your runtime to 32-bit (follow this guide) and see if that fixes your error.
