ADO NET Source works perfectly within BIDS but getting error in a SQL Agent Job

  • Hi,

    I have a SSIS Package with a ADO NET SOURCE for capturing data from MS Access DB which is in different Server.

    So, I have setup the System DSN in 32 bit ODBC Data Source Administrator and used .NET Provider\ODBC Data Provider connection manager in SSIS.

    This works perfectly within BIDS. However when I put this SSIS package in a SQL Agent Job, it does not work.

    I am getting the below error :

    ----------------------------------------------------------------------------------------

    Code: 0xC0047062 Source: DFT_Customer_DLT ADO NET Source [16]

    Description: System.Data.Odbc.OdbcException: ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, 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(String assemblyQualifiedName, String connStr, Object transaction) at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)

    End Error Error: 2013-07-31 03:29:50.19 Code: 0xC0047017

    Source: DFT_Customer_DLT SSIS.Pipeline Description: component "ADO NET Source" (16) failed validation and returned error code 0x80131937. End Error Error: 2013-07-31 03:29:50.19 Code: 0xC004700C Source: DFT_Customer_DLT SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2013-07-31 03:29:50.19 Code: 0xC0024107 Source: DFT_Customer_DLT Description: There were errors during task validation.

    -------------------------------------------------------------------------------------------

    As per my analysis there might be problem with 32bits and 64bits.

    Details of my project Environmenta nd Package :

    SERVER --> Windows Server 2008 R2 64 bit

    PACKAGE --> 32 bit

    ODBC data source --> 32 bit

    SQL Server Agent --> 64 bit. (I have also tried by changing the property with 32bit in Execution Option in SQL Server Agent.)

    What needs to be done to resolve this error for executing with SQL Agent Job?

    Any and All help will be greatly appreciated...Thanks

  • You need both a 32 bit AND 64 bit ODBC data source.

  • Hi SSC Veteran,

    Can u pls expalin more?

    I have tried to Setup the DSN in 64 bit ODBC data Source but i could not do the same.

    So, pls help me if you have faced the same error.

    Regards,

  • BIDS is a 32 bit application, so it uses the 32 bit ODBC driver. When the package is running under SQL Agent, that is a 64 bit process, so it will use the 64 bit ODBC driver.

  • There's two ODBC Administrators: 32 bit and 64 bit

    32 bit: %windir%\syswow64\odbcad32.exe

    64 bit: %windir%\system32\odbcad32.exe

  • Hi,

    I am agree with you but in SQL Server Agent if we make 32bit in Execution Option then it should take 32bit ODBC driver. I am not clear on this how it works.

    Thanks

  • ------------------------------------------------------

    There's two ODBC Administrators: 32 bit and 64 bit

    32 bit: %windir%\syswow64\odbcad32.exe

    64 bit: %windir%\system32\odbcad32.exe

    ------------------------------------------------------

    While I tried to configure 64bit ODBC data source in SYSTEM DSN I dint get any option for Access DB Driver.

    I tried with below .exe

    64 bit: %windir%\system32\odbcad32.exe

    Steps -> System DSN -> ADD -> In Create New Data Source Window I dont see any Driver for MS Access.

    Please check in the attachment.

    Let me know if anything I am missing.

    Thanks

  • You need to download and install 64 bit Access (Office) ODBC drivers separately. I don't have a link to a download page, but you can search for it.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply