ADO NET source does not in SQL Agent Job

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

ADO NET source does not in SQL Agent Job

Author

Topic

pinkuraj
Starting Member

1 Post

Posted - 2013-07-31 : 09:11:25

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

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2013-07-31 : 10:27:49

This may be of some help as there are no 32 bit drivers for this:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6d80822d-34a8-4d12-b9ad-92abf515a973/sql-server-64-bit-connecting-to-ms-access

dgh898
Starting Member

10 Posts

Posted - 2013-08-08 : 23:07:35

unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources