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

Question

Post reply

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

Raj_11

Say Hey Kid

Points: 711
More actions
July 31, 2013 at 3:05 am

#395231

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

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

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

SQL Surfer '66 SSCertifiable Points: 5161 More actions · Answer 1

SQL Surfer '66

SSCertifiable

Points: 5161

July 31, 2013 at 3:55 am

#1637288

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

Raj_11 Say Hey Kid Points: 711 More actions · Answer 2

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,

tim_harkin SSCrazy Points: 2399 More actions · Answer 3

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.

SQL Surfer '66 SSCertifiable Points: 5161 More actions · Answer 4

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

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

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

Raj_11 Say Hey Kid Points: 711 More actions · Answer 5

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

Raj_11 Say Hey Kid Points: 711 More actions · Answer 6

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

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

SQL Surfer '66 SSCertifiable Points: 5161 More actions · Answer 7

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.