SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 ADO NET source does not in SQL Agent Job
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pinkuraj
Starting Member

India
1 Posts

Posted - 07/31/2013 :  09:11:25  Show Profile  Reply with Quote
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

United Kingdom
3608 Posts

Posted - 07/31/2013 :  10:27:49  Show Profile  Reply with Quote
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

Go to Top of Page

dgh898
Starting Member

10 Posts

Posted - 08/08/2013 :  23:07:35  Show Profile  Reply with Quote
unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000