SQL Job failed because driver is not valid

0

I have a SSIS package that I want to schedule in a job in SQL Server 2008, the package runs perfectly by it's own, but when executed in a job it fails, I get the following error message:

Executed as user: XXXXXXX \ SYSTEM. Execute Package Utility for Microsoft (R) SQL Server Version 10.0.4000.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:48:09 AM Error: 18/06/2014 10:48:09.42 Code: 0xC0047062 Source: Data Flow Task ADO NET Source [1] Description: System.Data.Odbc.OdbcException: ERROR [HY024] [Microsoft] [ODBC Microsoft Access Driver] '(unknown)' is not a valid path. Make sure That the path name is spelled Correctly and That you are connected to the server on Which the file reside. ERROR [IM006] [Microsoft] [ODBC Driver Manager] SQLSetConnectAttr error ERROR [HY024] driver [Microsoft] [ODBC Microsoft Access Driver] '(unknown)' is not a valid path. Make sure That the path name is spelled Correctly and That you are connected to the server on Which the file reside. at System.Data.Odbc.OdbcConnection.HandleError (OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle .. ctor (OdbcConnection connection, built OdbcConnectionString, OdbcEnvironmentHandle environmentHandle) at System.Data.Odbc.OdbcConnectionOpen .. ctor (OdbcConnection outerConnection, OdbcConnectionString ConnectionOptions) at System.Data.Odbc.OdbcConnectionFactory.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.Odbc.OdbcConnection.Open () at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection (AssemblyQualifiedName String, String connStr, Object transaction) Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection at (Object pTransaction) Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections at (Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction) End Error Error: 18/06/2014 10:48:09.42 Code: 0xC0047017 Source: Data Flow Task SSIS.Pipeline Description: Error in validating component "ADO NET Source" (1); error code 0x80131937 was returned. End Error Error: 18/06/2014 10:48:09.42 Code: 0xC004700C Source: Data Flow Task SSIS.Pipeline Description: Failed to validate one or more components. End Error Error: 18/06/2014 10:48:09.42 Code: 0xC0024107 Source: Data Flow Task Description: There were errors during task validation. End DTExec error: package execution returned DTSER_FAILURE (1). Started: 10:48:09 AM Finished: 10:48:09 AM Elapsed: 0.219 seconds. Package execution failed. Unable to perform the step.

What the package does is to get data from a Microsoft Access data base inside a network drive(Z:) from a another server. And past the data to a table in a SQL data base.

The drivers and the server where I'm at are 64 bits, and I tried to run it with the "32 bit runtime" option in the job properties but I get the same message, and I do not really know what to do, I tracked the code errors on google but I don't find any solution. And as far as I know there shouldn't be any permissions problems, since in the server where I'm trying to create this job there's only one user and it's the administrator and also there are other jobs created by the same user, so I believe proxies are not needed , but this one is the only one that uses an Access ODBC.

If there's anything that I should check, please tell me.I really don't know what to do, I've been trying to get this thing working for almost a month and nothing. So if anyone could help me, I'll appreciate it.

sql
sql-server-2008
ms-access
ssis
odbc
asked on Stack Overflow Jun 19, 2014 by Luis KrÄke

1 Answer

0

After creating ODBC system sources you need to replace the network drive with the full path See this link

answered on Stack Overflow Feb 12, 2018 by Amine Zaminex

User contributions licensed under CC BY-SA 3.0