Solved

SQL 2008 Server Agent fails job based on architecture mismatch

Posted on 2011-02-16
3
1,462 Views
Last Modified: 2012-05-11
HI,

I have an SSIS package that works fine when i manually execute it in SQL Server 2008 via the Execute Package Utility (or via the BIDS) however I've set up an SQL Server Agent to run this package nightly and it fails every night with the below messages.

Executed as user: MS\administrator. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.2531.0 for 64-bit  
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    
Started:  10:50:03 AM  Error: 2011-02-15 10:50:11.43     Code: 0xC0047062    
Source: RETRIVE VWPostPhoneData ADO NET Source 1 [71]    
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: 2011-02-15 10:50:11.44    
Code: 0xC0047017    
Source: RETRIVE VWPostPhoneData SSIS.Pipeline    
Description: component "ADO NET Source 1" (71) failed validation and returned error code 0x80131937.  
End Error  Error: 2011-02-15 10:50:11.44    
Code: 0xC004700C    
Source: RETRIVE VWPostPhoneData SSIS.Pipeline    
Description: One or more component failed validation.  
End Error  Error: 2011-02-15 10:50:11.44    
Code: 0xC0024107    
Source: RETRIVE VWPostPhoneData      
Description: There were errors during task validation.  
End Error  DTExec: The package execution returned DTSER_FAILURE (1).  
Started:  10:50:03 AM  Finished: 10:50:11 AM  Elapsed:  7.937 seconds.  The package execution failed.  The step failed.
0
Comment
Question by:markserv
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 29

Accepted Solution

by:
mass2612 earned 500 total points
ID: 34912942
Hi,

when it works correctly are you running it from another machine (such as a workstation)? It might be a problem with the DSN setup. It might be that the DSN you have setup is using the 64 bit version which is what is under the Start menu shortcut.

Try setting up another connection using : -
c:\windows\sysWOW64\odbcad32.exe

How to Create an ODBC Connection for 32-bit application on 64-bit Windows
http://protegeit.blogspot.com/2009/10/how-to-create-odbc-connection-for-32.html

Managing Data Sources
http://msdn.microsoft.com/en-us/library/ms712362%28VS.85%29.aspx
0
 

Author Comment

by:markserv
ID: 35013702
Hi,

terribly sorry for the late reply on this one.

when i run the ssis package it's being run on the same machine that the package was made.
0
 

Author Closing Comment

by:markserv
ID: 35014221
Looks like it was compiled using an ODBC from the 64-bit tool. also needed to modify the SQL Server Agent to run in 32-bit mode.
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article explains how to install and use the NTBackup utility that comes with Windows Server.
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question