none
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

    Question

  • Hi Experts,

    I am working on  SQL SERVER 2008R2 version. My SSIS pacakge is using xlsx file to read data through the script. For this I have installed Access database engine and using the below syntax in the script.

    excelFile = Dts.Variables(

    "ExcelFile").Value.ToString

    connectionString =

    "Provider=Microsoft.ACE.OLEDB.12.0;" & _

     

    "Data Source=" & excelFile & _

     

    ";Extended Properties='Excel 12.0;HDR=YES';"

    excelConnection =

    New OleDbConnection(connectionString)

     

    excelConnection.Open()

     A strange thing is happening, When I am running directly the SSIS package. The package is running fine without any errors, but when I am triggering it through Sql Server Agent job on the same server it is giving me the error :

    Error: 2011-07-15 12:09:20.38
       Code: 0x00000001
       Source: Get ExcelTables
       Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidOperationException: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
       at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper)
       at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
       at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.OleDb.OleDbConnection.Open()
       at ScriptTask_9534937ca8d9411c863c39ba6b6d2252.vbproj.ScriptMain.Main()
       --- End of inner exception stack trace ---
       at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
       at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
       at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
       at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
       at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
    End Error
    DTExec: The package execution returned DTSER_FAILURE (1).

    

    Please help me to resolve the problem.

    Thanks

    Anki

    Friday, July 15, 2011 4:57 PM

Answers

All replies