SQL 2008 Server Agent fails job based on architecture mismatch

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.
markservAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mass2612Connect With a Mentor Commented:
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
 
markservAuthor Commented:
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
 
markservAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.